When using NOW() in MySQL can I be certain that the correct UTC value will be stored in a TIMESTAMP column?
date, mysql, time
Solution
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`.
Problem
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: - 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). - 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. - 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?