Timestamp in database for new row

[Guest]

I have a timestamp field in my db.
In my mysql program I clicked on the box that says "On Updated Current_Timestamp" which works fine for updating rows in the database and adds the time.
Now I want it to add the time for NEW rows, so in the default box I put now();  but I get an sql error. I am doing it in the database to lessen the load on coldfusion but maybe something like this is better in my cfquery insert into sql?:

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

What do you think I should do? Thanks

[Dan_Bracuk]

What is the code you are running and what is the error?

[Guest]

Hi Dan

The field UPDATED is a timestamp field, it has length 0 because it doesn't let me change it, decimals 0 and allow null.

The Default value I put in the mysql program for the field is: now();

It has the checkbox for On Update Current_Timestamp selected.

I get this error: 1067 'Invalid default value for value updated'

[Dan_Bracuk]

I did a google search on "mysql now" and found this page.  http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

I noticed one synonym for now is current_timestamp().  I'd try that with a 0 in the brackets.

[Guest]

Hi Dan I tried deleting the field UPDATED and then adding it again, I used the following three options:

1) current_timestamp()

2) current_timestamp(0)

3) current_timestamp(0);

But I get this error in mysql: '1067 Invalid default value for UPDATED'

[Dan_Bracuk]

Since I don't actually use mysql, and nobody else has answered, you might have better luck on a mysql forum.

[tclaremont]

It it is truly a timestamp field, there should be no need for a default value... no?

[Guest · Accepted solution]

Thanka for all the input I played around a lot with it and I made it work, this is what I did to the timestamp mysql field:

Field length 0.

Decimals 0

Allow null NO

Checkbox for On Update Current_Timestamp selected

Default value CURRENT_TIMESTAMP

It now works fine adding/updating the date for both a new and an exisiting record automatically

Thanks for all the help about dates