1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
|
#
# Tests of time zone handling in conjunction with daylight savings, DST.
# We currently use the CET time zone, which sets clock back one hour on
# the last Sunday of October, and sets it forward one hour on the last
# Sunday of March.
#
# Only times before 2021 are valid testing material, as The European
# Parliament's Transport and Tourism Committee has decided to end the
# seasonal clock change that year.
#
# See https://en.wikipedia.org/wiki/Summer_time_in_Europe.
#
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 );
# Daylight savings overlap, which occurs when clocks are set back one
# hour during the night of e.g. 2018-10-28, as happens for CET.
# One second after local time 02:59:59, the new local time is 02:00:00.
#
# The times here are in UTC (i.e. +00:00,) and can be mapped straight to
# CET, or CEST. 01:00 UTC corresponds to 03:00 CEST, but at this point
# in CEST time the clocks are set back and hence we find ourselves at
# 02:00 CET. Times after this show only a one hour displacement until
# 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;
a
2018-10-28 02:30:00
2018-10-28 02:59:00
2018-10-28 02:00:00
2018-10-28 02:30:00
# 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;
a
2018-10-28 02:30:00
2018-10-28 02:59:00
2018-10-28 02:00:00
2018-10-28 02:30:00
# Same test, but with an initial displacement from UTC in the hypothetical
# time zone with a displacement of 12:34 hours. This still maps
# one-to-one to the CEST times 13:56, 14:25, 14:26 and 14:56 on the
# 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;
a
2018-10-27 13:56:00
2018-10-27 14:25:00
2018-10-27 14:26:00
2018-10-27 14:56:00
# 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;
a
2018-10-27 13:56:00
2018-10-27 14:25:00
2018-10-27 14:26:00
2018-10-27 14:56:00
# Finally, a test with displaced times where the corresponding UTC time
# 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;
a
2018-10-28 02:30:00
2018-10-28 02:59:00
2018-10-28 02:00:00
2018-10-28 02:30:00
# 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;
a
2018-10-28 02:30:00
2018-10-28 02:59:00
2018-10-28 02:00:00
2018-10-28 02:30:00
#
# Daylight savings gap, occurs when clocks are set forwards one hour at
# 02:00. After 01:59, the next minute the clock is at 03:00.
#
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;
a
2019-03-31 01:30:00
2019-03-31 01:59:00
2019-03-31 03:00:00
2019-03-31 03:30:00
# 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;
a
2019-03-31 01:30:00
2019-03-31 01:59:00
2019-03-31 03:00:00
2019-03-31 03:30:00
DROP TABLE ts1, dt1, ts2, dt2, ts3, dt3, ts4, dt4;
SET time_zone = DEFAULT;
|
