Skip to content

Instantly share code, notes, and snippets.

@mrtns
Last active August 29, 2015 14:15
Show Gist options
  • Save mrtns/786d0a06d8ab7c304b41 to your computer and use it in GitHub Desktop.
Save mrtns/786d0a06d8ab7c304b41 to your computer and use it in GitHub Desktop.
MySQL Timezone Issue
# 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 |
+------------------------------------------------------------------------------+
*/
@mrtns
Copy link
Author

mrtns commented Feb 16, 2015

My MySQL instance is configured for the 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                      |
+----------------------------------------------------------------------------------------+
*/

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 get 2015-03-08 02:00:00, but I get 2015-03-08 03:00:00 instead:

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                                                           |
+------------------------------------------------------------------------------+
*/

I'm not sure why this is the case. What am I missing?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment