adding a timestamp field to a table (MySQL database)

[Dax_Trajero]

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 ?

[ilssac]

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.

[Dax_Trajero]

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...

[ilssac]

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.

[Dax_Trajero]

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.

[-__cfSearching__-]

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

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

<cfset myTimeStamp = myDate & " " &

myTime><br />

The correct way to handle date/time columns is to use date objects (not strings). The now() function returns a valid date/time object which can be passed it to your database "as is" - or preferably using cfqueryparam

INSERT INTO TableName (DateTimeCol) VALUES ( #now()# ) ...

INSERT INTO TableName (DateTimeCol)

VALUES ( <cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp"> )

Message was edited by: -==cfSearching==-