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

8
votes

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

3
mysqltimestamp
Tried it without wrapping creation_time with UNIX_TIMESTAMP? - Michael Robinson
Actually it's what I tried first, it didn't work either :-( - Maxime Laval
What happened? How did it not work? - ypercubeᵀᴹ
If you don't need the special features of TIMESTAMP why not change it to DATETIME? - staticsan
I need them, the thing is I need to populate this table with existing data first, then the timestamp the way I declare is exaclty what I want for future data. Otherwise I feel like it doesn't work for 2010-03-28 02:15:51 but it worked with 2010-10-11 11:11:11 for instance... - Maxime Laval

3 Answers

7
votes

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.

2
votes

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.

0
votes

If the column is always auto-updated, you can remove the property, getters and setters from the Entity. Doing this way, it will be ignored in all queries.