117 lines
4 KiB
Text
117 lines
4 KiB
Text
--echo #
|
|
--echo # Tests of time zone handling in conjunction with daylight savings, DST.
|
|
--echo # We currently use the CET time zone, which sets clock back one hour on
|
|
--echo # the last Sunday of October, and sets it forward one hour on the last
|
|
--echo # Sunday of March.
|
|
--echo #
|
|
--echo # Only times before 2021 are valid testing material, as The European
|
|
--echo # Parliament's Transport and Tourism Committee has decided to end the
|
|
--echo # seasonal clock change that year.
|
|
--echo #
|
|
--echo # See https://en.wikipedia.org/wiki/Summer_time_in_Europe.
|
|
--echo #
|
|
|
|
SET time_zone = 'CET';
|
|
|
|
CREATE TABLE ts1 ( a TIMESTAMP );
|
|
CREATE TABLE dt1 ( a DATETIME );
|
|
|
|
CREATE TABLE ts2 ( a TIMESTAMP );
|
|
CREATE TABLE dt2 ( a DATETIME );
|
|
|
|
CREATE TABLE ts3 ( a TIMESTAMP );
|
|
CREATE TABLE dt3 ( a DATETIME );
|
|
|
|
CREATE TABLE ts4 ( a TIMESTAMP );
|
|
CREATE TABLE dt4 ( a DATETIME );
|
|
|
|
--echo # Daylight savings overlap, which occurs when clocks are set back one
|
|
--echo # hour during the night of e.g. 2018-10-28, as happens for CET.
|
|
--echo # One second after local time 02:59:59, the new local time is 02:00:00.
|
|
--echo #
|
|
--echo # The times here are in UTC (i.e. +00:00,) and can be mapped straight to
|
|
--echo # CET, or CEST. 01:00 UTC corresponds to 03:00 CEST, but at this point
|
|
--echo # in CEST time the clocks are set back and hence we find ourselves at
|
|
--echo # 02:00 CET. Times after this show only a one hour displacement until
|
|
--echo # 2019-03-31, the last Sunday of March.
|
|
|
|
INSERT INTO ts1 VALUES ('2018-10-28 00:30:00+00:00'),
|
|
('2018-10-28 00:59:00+00:00'),
|
|
('2018-10-28 01:00:00+00:00'),
|
|
('2018-10-28 01:30:00+00:00');
|
|
|
|
SELECT * FROM ts1;
|
|
|
|
--echo # Repeated for DATETIME.
|
|
|
|
INSERT INTO dt1 VALUES ('2018-10-28 00:30:00+00:00'),
|
|
('2018-10-28 00:59:00+00:00'),
|
|
('2018-10-28 01:00:00+00:00'),
|
|
('2018-10-28 01:30:00+00:00');
|
|
|
|
SELECT * FROM dt1;
|
|
|
|
--echo # Same test, but with an initial displacement from UTC in the hypothetical
|
|
--echo # time zone with a displacement of 12:34 hours. This still maps
|
|
--echo # one-to-one to the CEST times 13:56, 14:25, 14:26 and 14:56 on the
|
|
--echo # preceding day.
|
|
|
|
INSERT INTO ts2 VALUES ('2018-10-28 00:30:00+12:34'),
|
|
('2018-10-28 00:59:00+12:34'),
|
|
('2018-10-28 01:00:00+12:34'),
|
|
('2018-10-28 01:30:00+12:34');
|
|
|
|
SELECT * FROM ts2;
|
|
|
|
--echo # Repeated for DATETIME.
|
|
|
|
INSERT INTO dt2 VALUES ('2018-10-28 00:30:00+12:34'),
|
|
('2018-10-28 00:59:00+12:34'),
|
|
('2018-10-28 01:00:00+12:34'),
|
|
('2018-10-28 01:30:00+12:34');
|
|
|
|
SELECT * FROM dt2;
|
|
|
|
--echo # Finally, a test with displaced times where the corresponding UTC time
|
|
--echo # is right around the daylight savings shift in CET.
|
|
|
|
INSERT INTO ts3 VALUES ('2018-10-27 23:06:00-01:24'),
|
|
('2018-10-27 23:06:00-01:53'),
|
|
('2018-10-27 23:06:00-01:54'),
|
|
('2018-10-27 23:06:00-02:24');
|
|
|
|
SELECT * FROM ts3;
|
|
|
|
--echo # Repeated for DATETIME.
|
|
|
|
INSERT INTO dt3 VALUES ('2018-10-27 23:06:00-01:24'),
|
|
('2018-10-27 23:06:00-01:53'),
|
|
('2018-10-27 23:06:00-01:54'),
|
|
('2018-10-27 23:06:00-02:24');
|
|
|
|
SELECT * FROM dt3;
|
|
|
|
--echo #
|
|
--echo # Daylight savings gap, occurs when clocks are set forwards one hour at
|
|
--echo # 02:00. After 01:59, the next minute the clock is at 03:00.
|
|
--echo #
|
|
|
|
INSERT INTO ts4 VALUES ('2019-03-31 00:30:00+00:00'),
|
|
('2019-03-31 00:59:00+00:00'),
|
|
('2019-03-31 01:00:00+00:00'),
|
|
('2019-03-31 01:30:00+00:00');
|
|
|
|
SELECT * FROM ts4;
|
|
|
|
--echo # Repeated for DATETIME.
|
|
|
|
INSERT INTO dt4 VALUES ('2019-03-31 00:30:00+00:00'),
|
|
('2019-03-31 00:59:00+00:00'),
|
|
('2019-03-31 01:00:00+00:00'),
|
|
('2019-03-31 01:30:00+00:00');
|
|
|
|
SELECT * FROM dt4;
|
|
|
|
DROP TABLE ts1, dt1, ts2, dt2, ts3, dt3, ts4, dt4;
|
|
|
|
SET time_zone = DEFAULT;
|