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

Question

MySQL has two types of data that are designed to store date and time values ​​- DATETIMEand TIMESTAMP. None of them store time zone information, and both have different rules.

Column

A DATETIMEwill store the exact date and time specified in the input request. (No conversions and no permissions for the time zone)

Column

A TIMESTAMPconverts the date and time value specified when pasting from the time zone of the connection during input to UTC. When retrieved, it converts the date and time value stored in UTC to the time zone of the search connection. The time zone of both connections can be explicitly or implicitly set in accordance with these rules .

Now, before I get my question, we will consider some of the nuances of processing dates and times when daylight saving time is involved. To summarize the answers to another Stack Overflow question , as well as what I understand from the MySQL Documentation regarding date / time:

• When using a column DATETIMEand explicitly specifying a value (i.e. / 2009-11-01 01:30:00), the value may be ambiguous. DATETIMEdoes not carry out conversations and simply saves this exact date / time. Say I'm in New York (which follows daylight saving time). As in the insertion, and in the search, I can not indicate / know whether this value refers to 1:30 in the morning at the time of daylight saving time (UTC-4) or at 1:30 in the morning when there is no daylight (UTC-5) .
• When using a column DATETIMEalong with NOW(), NOW()evaluates the date and time value at the beginning of the query (i.e. / 2009-11-01 01:30:00), and this value is inserted without conversion into the field DATETIME, causing the exact exact ambiguity that is indicated above.
• TIMESTAMP (../2009-11-01 01:30:00), , . , 1:30 .

, :

MySQL, , (, America/New York), , NOW() TIMESTAMP UTC ? UTC, , , UTC 1:30 - UTC - 1:30 .

: UTC , UTC/from-UTC / TIMESTAMP? , 1:30 (America\New York ), UTC-4 1:30 (America\New York ) m UTC-5 - TIMESTAMP, 2009-11-01 01:30:00 NOW()? , MySQL, , ( ), () UTC ?

Answer 1

, :

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

SELECT NOW() UTC, .

, , system_time_zone - Pacific Daylight Time ( - ), .

, INSERTing SELECTing a DATE DATETIME. .

TIMESTAMP , , / UTC. , , , UTC, ; , .

, - DATETIME TIMESTAMP, , , . SELECT.

Answer 2

time_zone SET time_zone:

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

, , UTC ( timestamp).

Answer 3

, . , UTC_TIMESTAMP(), UTC. , , , /.

, / , . (, , , ).