TIMESTAMP Field - UTC <> Local TimeZone

Question

Referring to Timestamp Lookups Documentation

https://dev.mysql.com/doc/refman/8.3/en/timestamp-lookups.html

In the documentation there is an example how UTC to Local TimeZone can cause descrepenices

Specically this calculation : Documentation verbiage The following example shows distinct UTC values that become identical in a non-UTC time zone:

mysql> CREATE TABLE tstable (ts TIMESTAMP);
mysql> SET time_zone = 'UTC'; -- insert UTC values
mysql> INSERT INTO tstable VALUES
       ('2018-10-28 00:30:00'),
       ('2018-10-28 01:30:00');
mysql> SELECT ts FROM tstable;
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 00:30:00 |
| 2018-10-28 01:30:00 |
+---------------------+
mysql> SET time_zone = 'MET'; -- retrieve non-UTC values
mysql> SELECT ts FROM tstable;
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+

First and foremost

DST switch happened on March 25th @ 2 AM , meaning MET Time @ 2 AM became 3 AM as shown in screenshot below

Not lets look at the caluation ::

When the timezone is changed to non-UTC that MET which is 1 hour ahead of UTC

MET	UTC	DST Applied
10/25 00:00	10/24 23:00	NO
10/25 01:00	10/25 00:00	NO
10/25 01:59	10/25 00:59	NO
10/25 03:00	10/25 01:00	YES 2AM -- forward by 1 hour -- so it becomes 3 AM

So 00:30 UTC --> MET Time should be 01:30 -- 1 hour ahead before 2AM MET and 01:30 should become 02:30 or 03:30 ?

There is no time posting of 2AM to 2:59AM in MET for the day light switch. It will show 3AM as timestamp.

Where is bug in my analysis ?

Correct my calulations
Mysql8 documnentation has to be corrected

Marcin Orlowski · Accepted Answer · 2024-06-17 07:49:17Z

1

You forgot to account for end of the DST. So March is start month for it but DST ends in October, therefore clock moves forward first, effectively "losing" an hour (which can result in a "jump" in local time i.e. from 1:59 AM directly to 3:00 AM as there's no 2:xx AM). Then, in October, clock moves backward, effectively "gaining" an hour. That can cause an hour to "repeat" in local time, as opposed to "lost" hour example. So your SQL examples are perfect illustration of and are just fine.

edited Jun 17, 2024 at 7:49

answered Mar 28, 2024 at 5:31

Marcin Orlowski

75.7k11 gold badges128 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

TIMESTAMP Field - UTC <> Local TimeZone

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related