Last active
August 29, 2015 14:15
-
-
Save mrtns/786d0a06d8ab7c304b41 to your computer and use it in GitHub Desktop.
MySQL Timezone Issue
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# MySQL is in UTC timezone | |
SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone, TIMEDIFF(NOW(), UTC_TIMESTAMP); | |
/* | |
+----------------------------------------------------------------------------------------+ | |
|@@global.time_zone|@@session.time_zone|@@system_time_zone|TIMEDIFF(NOW(), UTC_TIMESTAMP)| | |
+----------------------------------------------------------------------------------------+ | |
|SYSTEM |SYSTEM |UTC |00:00:00 | | |
+----------------------------------------------------------------------------------------+ | |
*/ | |
# But for some reason when I hadd 1 hour to 2015-03-08 01:00:00 I get 2015-03-08 03:00:00, when I expect 2015-03-08 02:00:00 | |
# Context: Sun, Mar 8, 2:00 AM is the time change from PST → PDT, which will move +1 hour (DST start): from UTC-8h to UTC-7h | |
SELECT TIMESTAMPADD(HOUR, 1, STR_TO_DATE('2015-03-08 01:00:00', '%Y-%m-%d %H:%i:%s')); | |
/* | |
+------------------------------------------------------------------------------+ | |
|TIMESTAMPADD(HOUR, 1, STR_TO_DATE('2015-03-08 01:00:00', '%Y-%m-%d %H:%i:%s'))| | |
+------------------------------------------------------------------------------+ | |
|2015-03-08 03:00:00 | | |
+------------------------------------------------------------------------------+ | |
*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
My MySQL instance is configured for the
UTC
timezone:My expectation is that date math (such as using
TIMESTAMPADD
) would be done in UTC.As an example, Sun, Mar 8, 2:00 AM is the time change from PST → PDT, which will move PT +1 hour (DST start): from UTC-8h to UTC-7h.
So when I add 1 hour to
2015-03-08 01:00:00
I expect to get2015-03-08 02:00:00
, but I get2015-03-08 03:00:00
instead:I'm not sure why this is the case. What am I missing?