Edit - Database Administrators Stack Exchange

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

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?

Joe Hopfgartner
– Joe Hopfgartner

2017-01-16 12:54:57 +00:00
Commented Jan 16, 2017 at 12:54
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.

Michael - sqlbot
– Michael - sqlbot

2017-01-17 00:11:54 +00:00
Commented Jan 17, 2017 at 0:11
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.

Michael - sqlbot
– Michael - sqlbot

2017-01-17 00:15:14 +00:00
Commented Jan 17, 2017 at 0:15
If I do "set time_zone='UTC';" in the session, but server time zone is still CET, I will be fine?

Joe Hopfgartner
– Joe Hopfgartner

2017-01-17 02:33:40 +00:00
Commented Jan 17, 2017 at 2:33
Yes. That should be totally fine.

Michael - sqlbot
– Michael - sqlbot

2017-01-17 03:02:35 +00:00
Commented Jan 17, 2017 at 3:02

Add a comment |

Correct minor typos or mistakes
Clarify meaning without changing it
Add related resources or links
Always respect the author’s intent
Don’t use edits to reply to the author

create code fences with backticks ` or tildes ~
```
like so
```
add language identifier to highlight code
```python
def function(foo):
print(foo)
```
put returns between paragraphs
for linebreak add 2 spaces at end
_italic_ or **bold**
indent code by 4 spaces
backtick escapes `like _so_`
quote by placing > at start of line
to make links (use https whenever possible)

<https://example.com>

[example](https://example.com)

<a href="https://example.com">example</a>

formatting help »
answering help »