Mysql date ok but signaled as incorrect

Question

I am trying to insert the date below into my TIMESTAMP column but it's unexpectedly signaled as incorrect format by mysql :

INSERT INTO `assets`( `updated_at`) VALUES ('2022-03-27 02:12:31.217573')

If i add or substract one day or one hour then the date is executed without error. I going crazy ^^

Thx for your help mysql version : 5.7.36

Answer 1

timestamp columns are weird. They effectively store a utc time, but on input and output convert to the timezone of the connection. Presumably you are using a timezone that has daylight savings where the second after 2022-03-27 01:59:59 is 2022-03-27 03:00:00, so your entered time is invalid.

If you intend to be storing times that are already in UTC (which you should!) you need to set your timezone appropriately. See https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html. But be aware that then all previously entered times will appear to change.

I much prefer to use datetime columns which will store and show exactly the data entered, though they have their own quirks.