7

MySQL has two data types that are purpose built for storing date & time values - DATETIME and TIMESTAMP. Neither type stores timezone information, and both have different rules.

A DATETIME column will store the exact date & time value provided in the insertion query. (No converstions and no affordances for time zone)

A TIMESTAMP column will convert the date & time value provided at insertion from the timezone of the connection at insertion time to UTC. On retrieval it will convert the date & time value stored from UTC to the timezone of the retrieval connection. The timezone of both connections can be explicitly or implicitly set according to these rules.

Now before I get to my question let's look at some of the nuances of handling dates & times when daylight savings is involved. Summarizing the answers on another Stack Overflow question as well as what I understand from the MySQL documentation regarding date/time:

  1. When using a DATETIME column and explicitly specifying a value (ie/ 2009-11-01 01:30:00), the value can be ambiguous. DATETIME performs no conversation and simply stores this exact date/time. Say I'm in New York (which follows a daylight savings time). Both at insertion and retrieval I have no way of indicating/knowing if this value refers to 1:30AM at the with-daylight-savings moment (UTC-4) or 1:30AM at the without-daylight-savings moment (UTC-5).
  2. When using a DATETIME column along with NOW(), NOW() evaluates to the date & time value at the start of query execution (ie/ 2009-11-01 01:30:00) and this value is inserted, with no converstions, into the DATETIME field causing the same exact ambiguity as mentioned above.
  3. When using a TIMESTAMP column and explicitly specifying a value (ie/ 2009-11-01 01:30:00), I again have the same problem as mentioned above. There's no way to specify and no way to know which 1:30am I'm referring to.

Now, here's my question:

Given a MySQL connection that is set to a timezone that includes daylight savings (say America/New York), can I be certain that inserting NOW() into a TIMESTAMP column will cause the correct UTC date & time value to be stored? UTC of course does not observe daylight savings, so the UTC time at the 1:30am New York timezone with-daylight-savings moment is different from the UTC time at the 1:30AM New York timezone without-daylight-savings moment.

More specifically: Is the UTC offset of the connection timezone at the start of query execution what is used to perform the to-UTC/from-UTC conversion when I insert/select from a TIMESTAMP column? Going back to my example, at the 1:30am with-daylight-savings moment (America\New York timezone) I'm at UTC-4 and at the 1:30am without-daylight-savings moment (America\New York timezone) I'm at UTC-5 - so in both these moments, would a different value be stored in a TIMESTAMP field when I explicitly insert 2009-11-01 01:30:00 or implicitly insert this same value by using NOW()? Finally, if I'm within a single MySQL connection that spans both these moments, and I execute two queries (one in the first moment, and a separate one in the second moment), will both queries cause the correct (different) UTC value to be stored?

Community
  • 1
  • 1
VKK
  • 882
  • 7
  • 19
  • 1
    Have you read [this](http://stackoverflow.com/questions/1646171/mysql-datetime-fields-and-daylight-savings-time-how-do-i-reference-the-extra) and [that](http://www.webdevelopersdiary.com/blog/good-to-know-how-to-properly-store-date-and-time-values-in-mysql) ? – Thomas G May 22 '17 at 10:29
  • Database UTC. The presentation layer user preference and timezone. – danny117 May 23 '17 at 14:30

4 Answers4

3

You might want this for a server:

mysql> SHOW VARIABLES LIKE '%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |  -- Comes from OS
| time_zone        | SYSTEM |  -- Probably the 'right' setting
+------------------+--------+

With those settings, SELECT NOW() will deliver UTC time, not local time.

For your personal computer, it is probably better to have system_time_zone equal to something like Pacific Daylight Time (or whatever), to reflect your current location.

No conversion happens when INSERTing or SELECTing a DATE or DATETIME. Think of it as being a picture of the clock.

For TIMESTAMP, whatever you give it is converted to/from UTC. That is, the bits stored in the table are UTC, but you can't see that; you only see the converted date&time based on the two settings above.

I suggest that the best way to get the answer is to create a table with a DATETIME and a TIMESTAMP, set the two settings, then see what happens when storing. Then change the settings and do a SELECT.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thanks for the answer but I'm specifically looking to see how location-based timezones (such as America\New York) are stored in TIMESTAMP fields with respect to when the location is in daylight savings and not in daylight savings. – VKK May 20 '17 at 15:46
  • The bits in the `TIMESTAMP` field are UTC. That is, at any given second in the universe, any store into a ts field will convert according to time and the zone setting in effect for the `INSERTer`. When `SELECTing`, it will be reconverted by the the selecter. If it is the same computer with the same settings, you get exactly what you stored. Meanwhile, someone in a different zone/DST/etc will see a different value. – Rick James May 20 '17 at 15:54
  • Maybe you need to fabricate an example showing some time and zone, and the inserts performed, together with the desired results of selects. – Rick James May 20 '17 at 15:55
  • "If it is the same computer with the same settings, you get exactly what you stored." I'm not really sure if that's true. I can be in the same timezone and have two different UTC offsets based on whether I'm in the with daylight savings or without daylight savings moment. That's kind of what I'm getting at. For example, in the daylight savings moment I might be at UTC-4, and in the without daylight savings moment I may in UTC-5. My question is not how to construct a database scheme that avoids this issue - I'd like to understand how it works. – VKK May 21 '17 at 15:46
  • The conversion is based on the DST setting _for the time in question_, not the _current time_. – Rick James May 21 '17 at 16:11
  • Does a timezone such as Americas/New York then have a single DST setting? If so how is Daylight Savings handled for such a timezone since the timezone is sometimes in and sometimes not in daylight savings? – VKK May 21 '17 at 16:18
  • A "timezone" includes an algorithm for whether to, and when to, switch to/from DST. A clue to this is that that no timezone name mentions DST. https://superuser.com/questions/668913/timezone-issue-during-daylight-saving (There are probably better references somewhere.) – Rick James May 21 '17 at 16:37
  • ``About a decade ago the previous science-challenged US President decided to change the start/end dates of DST. That led to a million dollars being spent updating timzone tables in a zillion computers around the million. Probably more energy was spent on the change that was allegedly saved.`` – Rick James May 23 '17 at 15:06
1

As far as I know, you're best off with doing this from your code. If you really have to do it via DB, you could use UTC_TIMESTAMP() which will always give you the current time based on UTC. Relying on your server timezone however is not a good idea in my opinion because the servers are bound to undergo changes as you grow/scale.

On the other hand, specifying the time from code will tend to be more important/accurate for you than letting it hit the DB. (Think latency, delayed inserts and what not).

Chibueze Opata
  • 9,856
  • 7
  • 42
  • 65
0

You can test the time_zone change by using the SET time_zone function :

mysql> create table demo (test timestamp);
mysql> SET time_zone='-06:00';
mysql> insert into demo VALUES(NOW());
mysql> SELECT * FROM demo;
+---------------------+
| test                |
+---------------------+
| 2017-05-23 08:55:16 |
+---------------------+

mysql> SET time_zone='+02:00';
mysql> insert into demo VALUES(NOW());
mysql> SELECT * FROM demo;
+---------------------+
| test                |
+---------------------+
| 2017-05-23 16:55:16 |
| 2017-05-23 16:55:32 |
+---------------------+

According to those results changing the timezone gives consistent results which prove that times are stored UTC (when using timestamp).

Adam
  • 17,838
  • 32
  • 54
  • Nice technique. Now try it with `(test DATETIME)` also. – Rick James May 23 '17 at 15:03
  • @RickJames With a `DATETIME` you will have 8 hours difference on the last select which proves that times are not stored UTC when using a `DATETIME`. – Adam May 23 '17 at 15:35
  • Does changing the time_zone change the "8"? – Rick James May 23 '17 at 16:02
  • @RickJames Switching from -03:00 time_zone to +02:00 will give you 5 hours difference with a `datetime` and 0 with a timestamp. The best thing to do is to test by yourself. – Adam May 23 '17 at 16:35
0

I used this one Not sure if it will helpful or not. Please add the below text in your my.cnf file.

[mysqld_safe]
timezone = UTC
Chinmay235
  • 3,236
  • 8
  • 62
  • 93