What exactly IS Time Stamping?

[Guest]

Hi,

I'm building a web application in an effort to create something not only practical to use, but to also improve my programming abilities.

At the moment, I store records in a database with separate columns for Time and Date.

What I want to know is, is this a good way about doing things? I have tried to research the best way to store dates and times but can't find much on the subject for a beginner like me.

I have often seen the format:

{ts '2008-04-10 20:13:00'}

...floating around on the web - is this the proper way to maintain a time stamp?

I simply want to do things properly from the ground up so would appreciate your advice and guidence on how to handle the storage of dates and times. If you could spare a minute to explain the best practice and pitfalls of this it would be much appreciated.

Apologies if I sound really stupid!

Thanks,
Mikey.

[Newsgroup_User]

Kapitaine wrote:
>
> ...floating around on the web - is this the proper way to maintain a time
> stamp?


Generally the best way is to store the value in a single data-time field
in ones database.

If one is storing the date and time seperately, and has formated strings
rather then a date-time value, it is very cumbersome to use these for
date and|or time calculations.

If one stores them separately but as as date-time fields then one wastes
a small amount of database space. Since even though only the date part
is significant, there is time data set to 00:00:00 in the date field.
Conversely, in the time field the date is also represented.

HTH
Ian

[Steve Sommers]

This would depend on the type of database you are using. MS-SQL has a datetime field which natively stores this as a floating point number but you never (normally) see this. Interestingly, storing the date and time as two separate columns still stores data as a floating point number. I've always used a single datetime column and never have run into a problem.

[Guest]

Hi, thanks for the reply.

So would it actually sit in my DB column as:

{ts '2008-04-10 20:13:00'}

Then do CF functions like this? e.g:

<cfset variables.test = "{ts '2008-04-10 20:13:00'}" />
<h2>#dateFormat(variables.test, "dd/mm/yyyy")#</h2>
<h2>#timeFormat(variables.test, "hh:mm:ss tt")#</h2>

Seems to work, but is this correct? Should the date and time be stored toether like that? Why does it need curly braces and a "t". Is this some kind of convention?

Thanks,
Mikey.

[Newsgroup_User]

Kapitaine wrote:
> Hi, thanks for the reply.
>
> So would it actually sit in my DB column as:
>
> {ts '2008-04-10 20:13:00'}
>
> Then do CF functions like this? e.g:
>
> <cfset variables.test = "{ts '2008-04-10 20:13:00'}" />
> <h2>#dateFormat(variables.test, "dd/mm/yyyy")#</h2>
> <h2>#timeFormat(variables.test, "hh:mm:ss tt")#</h2>
>
> Seems to work, but is this correct? Should the date and time be stored toether
> like that? Why does it need curly braces and a "t". Is this some kind of
> convention?
>
> Thanks,
> Mikey.
>

Well, no, that is a string that ColdFusion typeless automatic conversion
is allowing to work for you. You exact example would probably be better
down as:

<cfset variables.test = createDateTime(2008,4,10,20,13,00)>

Now you have an actual date-time object variable that is easily
accessible for all date-time functions, calculations and formating.

[Guest]

Is there any way I can use a dynamic date and time with the createDateTime() function?

I tried:

<cfset variables.myNow = LSDateFormat(now(),"yyyy,mm,dd") & LSTimeFormat(now(),"HH,mm,dd") />
<cfset variables.test = createDateTime(variables.myNow) />

But it errors.

Thanks,
Mikey.

[Dan_Bracuk]

If you have a choice between date, time, and timestamp fields, use the one that's most appropriate for the field. If it's a simple matter of indicating when a record was entered or last updated, then a timestamp field is what I would use.

[Newsgroup_User]

Kapitaine wrote:
> Is there any way I can use a dynamic date and time with the createDateTime()
> function?

A quick check of the documentation would answer this.

The createDateTime() function looks like this:

Function syntax
CreateDateTime(year, month, day, hour, minute, second)

As usual these parameters can either be literal values or variables
containing appropriate values.

[Guest]

Yes, sorry, my apologies. I forgot to explain that I had seen the docs, but I wondered if there was a way to do it in one function like now() with a dateFormat() wrapped around it.

Not to worry. I seem to have sorted my issues out anyway.

Many thanks to everyone for their help!
Cheers,
Mikey.

[Newsgroup_User]

Kapitaine wrote:
> Yes, sorry, my apologies. I forgot to explain that I had seen the docs, but I
> wondered if there was a way to do it in one function like now() with a
> dateFormat() wrapped around it.
>

If you are looking to turn a data string into a date variable object,
then you are looking for the ParseDateTime() and|or LSParseDateTime()
functions. Just realize that parsing date strings is less precise
because the same string can equal different dates to different people
around the world. I.E. 2/3/2008, can mean February 3rd, 2008 to some
and March 2nd, 2008 to others.

[Dan_Bracuk]

For inserting the current timestamp into a timestamp field, the best option is to use a db function that returns the current timestamp. If your db does not have one, that would be strange, but you could always use cold fusion's now() function.