Skip to main content
D
Dax_Trajero
Inspiring
May 25, 2010
Question

adding a timestamp field to a table (MySQL database)

  • May 25, 2010
  • 1 reply
  • 2733 views

I've defined a field in a MySQL table as type 'timestamp'

eg. 2010-05-25 21:36:19

When I perform an insert using coldfusion, the row doesn't change the value in the timestamp automatically

Am I better creating my own timestamp field, type varchar and manually insert my timestamp values ?

This topic has been closed for replies.

1 reply

ilssac
ilssac
Inspiring
May 25, 2010

No, it is a pretty bad idea to use a text field to store date-time information.  It makes it much harder to do date and|or time calculations on the data.

A timestamp field does not mean you have a field that automatically inserts a timestamp on record insert and|or update.  Just that you have a field that expects a 'timestamp' value which is usually a date-time value down to the millisecond precision.

You can set up the database to automatically create this value when data is inserted and|or updated, but that is more then just creating the timestamp field.  And the specifics will change depending on your exact database management system.  Searching the internets for information on your database managerment system of choice and auto and|or default values for fields should guide you in the correct direction.

Or you can insert timestamp data along with the other data you are inputting into the database.  The now() function combined with the <cfqueryparam value="#timeData#" cfsqltype="cf_sql_timestamp"> is a useful combination to do this.

D
Dax_TrajeroAuthor
Inspiring
May 25, 2010

Thanks Ian

What I've done is create a table field, type 'datetime'

Now whenever I do an insert with Coldfusion I include the latest time, worked out this way:

<cfset myDate = DateFormat(Now(),'yyyy-mm-dd')>

<cfset myTime = TimeFormat(Now(),'HH-mm-ss')>

<cfset myTimeStamp = myDate & " " & myTime><br />

Using MySQL's datetime field format ensures values can be sorted correctly, etc...

D
Dax_TrajeroAuthor
Inspiring
May 25, 2010

DateFormat() is meaningless to the computer it is only needed when you want a human to easily understand the value.  You are basically taking the computers date-time value, converting it to a string that humans could understand easily, then converting it back to a computer date-time value.

<cfset myTimeStamp = now()> would surfice for anything you want to insert into a database date-time field.

Or just using the now() function directly in the SQL block if you don't plan to use the myTimeStamp variable anywhere else in your code.


Thanks both of you. I'm using the Now() function directly in the INSERT statement.

It's working fine!

Could've sworn I tried that earlier on, but must be mistaken. I think I must've tried it with a MySQL field type 'timestamp' and it failed to work.

Big thanks guys.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices