MySQL: Trying to insert a value in a timestamp throws an error

Question

I have a table with this column:

last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

And it looks like I can not insert a row with a custom timestamp, I get this error:

Incorrect datetime value: '1145868501' for column 'last_modified' at row 1

I am trying to populate this table with data coming from another table, that other table only has a creation_time field which is a DATETIME so I use UNIX_TIMESTAMP(creation_time) to populate the timestamp.

I think the timestamp column with "DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" prevents me from inserting my own stuff, am I right? If yes where is the official doc about that, and what is the best solution? Creating a simple timestamp first then alter the table after inserting data?

Thanks!

EDIT: since people are advising me to not use UNIX_TIMESTAMP, I have to say that I didn't want to use that at the beginning, but I got this kind of error: Incorrect datetime value: '2010-03-28 02:15:51' for column 'last_modified' So I thought I had to insert a "real" timestamp...

Answer 1

You can explicitedly insert a value in a TIMESTAMP column. Read: TIMESTAMP Properties

The auto-update TIMESTAMP column, if there is one, is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. If all other columns are set to their current values, the TIMESTAMP column does not change. Automatic updating does not apply if the TIMESTAMP column is explicitly assigned a value other than NULL.

---

Update

Hehe, the error occurs because - well- there was no datetime with '2010-03-28 02:15:51'! This was in the daylight saving time gap (which usually appears some day in March, between 02:00 - 03:00 or 03:00 - 04:00.

See: Daylight Saving Time explanation.

Answer 2

You're trying to put a long integer into a datetime field. That doesn't work. Remove the call to UNIX_TIMESTAMP() and it should work.

The MySQL TIMESTAMP type is almost identical to a DATETIME; it just has some extra auto-update magic. As far as SELECT and UPDATE is concerned, it is a DATETIME.