CODEWITHSUNDEEP
mysqldatetime
VKK
VKK

Reputation: 912

When using NOW() in MySQL can I be certain that the correct UTC value will be stored in a TIMESTAMP column?

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?

Upvotes: 7

Views: 3186

Answers (4)

Chinmay235
Chinmay235

Reputation: 3414

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

Upvotes: 0

Chibueze Opata
Chibueze Opata

Reputation: 10044

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).

Upvotes: 1

Adam
Adam

Reputation: 18855

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).

Upvotes: 0

Rick James
Rick James

Reputation: 142453

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.

Upvotes: 3

Related Questions