Skip to main content
A
Anonymous
April 10, 2008
Question

What exactly IS Time Stamping?

  • April 10, 2008
  • 6 replies
  • 539 views
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.
    This topic has been closed for replies.

    6 replies

    D
    Dan_Bracuk
    Inspiring
    April 11, 2008
    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.
    N
    Newsgroup_User
    Inspiring
    April 10, 2008
    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.
    A
    Anonymous
    April 10, 2008
    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.
    D
    Dan_Bracuk
    Inspiring
    April 10, 2008
    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.
    A
    Anonymous
    April 10, 2008
    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.
    S
    Steve Sommers
    Legend
    April 10, 2008
    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.
    A
    Anonymous
    April 10, 2008
    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.
    N
    Newsgroup_User
    Inspiring
    April 10, 2008
    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
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices