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.
|||
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
|||
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.
|||
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.
|||
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.
|||
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.
|||
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.
|||
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.
|||
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.
|||
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.
|||
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.
Related posts:
- Date and Time Issue in CF7
- the date and time in Hebrew?
- compare date/time in record to server time?
- Locale Date and Time display
- date and time variables
Related posts brought to you by Yet Another Related Posts Plugin.
