Timeline for answer to Why does MySQL calculate a wrong timestamp when using FROM_UNIXTIME by Michael - sqlbot
Current License: CC BY-SA 3.0
Post Revisions
8 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 18, 2017 at 8:39 | history | migrated | from stackoverflow.com (revisions) | ||
| Jan 17, 2017 at 4:00 | vote | accept | Joe Hopfgartner | ||
| Jan 17, 2017 at 3:02 | comment | added | Michael - sqlbot | Yes. That should be totally fine. | |
| Jan 17, 2017 at 2:33 | comment | added | Joe Hopfgartner | If I do "set time_zone='UTC';" in the session, but server time zone is still CET, I will be fine? | |
| Jan 17, 2017 at 0:15 | comment | added | Michael - sqlbot | If your session is in a time zone which has transitions, this double-conversion is what creates the ambiguity -- there is an entire hour of timestamps that is equivalent to two UTC times during the autumnal transition (out of DST)... and an entire hour of timestamps that are not equivalent to any UTC timestamp during the vernal transition (into DST). Entering will not bite you with this particular ambiguity -- the ambiguity is from the wall clock moving back one hour and encountering the same 3600 seconds again when leaving DST. | |
| Jan 17, 2017 at 0:11 | comment | added | Michael - sqlbot |
Correct, it is the session (connection) time zone that matters most directly, because, when set, it overrides the system zone. FROM_UNIXTIME() converts the integer epoch time and converts it to a datetime literal in the session time zone. Insert/update to a TIMESTAMP then converts that datetime literal from the session time one back to UTC, which is how timestamps are stored in MySQL.
|
|
| Jan 16, 2017 at 12:54 | comment | added | Joe Hopfgartner | Thanks so much. So essentially from_unixtime takes the timestamp, converts it to a datetime expression that is ambigous, and when it gets converted again to be stored, the wrong value is used? So long story short, updating a timestamp field from a timestamp can only be done if the connection time zone is utc? It does not help if the server time zone is UTC if the result of from_unixtime is ambigous in the connection time zone? | |
| Jan 16, 2017 at 2:01 | history | answered | Michael - sqlbot | CC BY-SA 3.0 |