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
|
CREATE TABLE ts (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
ts TIMESTAMP);
SELECT @@GLOBAL.TIME_ZONE;
@@GLOBAL.TIME_ZONE
SYSTEM
Insert timestamps covering skip hour at the start of DST for CET
INSERT INTO ts(ts) VALUES
('2020-03-28 23:40:00+00:00'),
('2020-03-29 00:00:00+00:00'),
('2020-03-29 00:20:00+00:00'),
('2020-03-29 00:40:00+00:00'),
('2020-03-29 01:00:00+00:00'),
('2020-03-29 01:20:00+00:00'),
('2020-03-29 01:40:00+00:00'),
('2020-03-29 02:00:00+00:00'),
('2020-03-29 02:20:00+00:00');
Expect the skip hour between 2:00-3:00
SELECT id, ts, UNIX_TIMESTAMP(ts) FROM ts;
id ts UNIX_TIMESTAMP(ts)
1 2020-03-29 00:40:00 1585438800
2 2020-03-29 01:00:00 1585440000
3 2020-03-29 01:20:00 1585441200
4 2020-03-29 01:40:00 1585442400
5 2020-03-29 03:00:00 1585443600
6 2020-03-29 03:20:00 1585444800
7 2020-03-29 03:40:00 1585446000
8 2020-03-29 04:00:00 1585447200
9 2020-03-29 04:20:00 1585448400
TRUNCATE TABLE ts;
Insert timestamps covering the repeat hour at the end of DST for CET
INSERT INTO ts(ts) VALUES
('2020-10-24 23:40:00+00:00'),
('2020-10-25 00:00:00+00:00'),
('2020-10-25 00:20:00+00:00'),
('2020-10-25 00:40:00+00:00'),
('2020-10-25 01:00:00+00:00'),
('2020-10-25 01:20:00+00:00'),
('2020-10-25 01:40:00+00:00'),
('2020-10-25 02:00:00+00:00'),
('2020-10-25 02:20:00+00:00');
Time difference between 2 consecutive rows should be 1200(20 min)
CREATE TABLE diffs (i INT, CONSTRAINT i CHECK (1200 = i) ENFORCED);
INSERT INTO diffs (SELECT unix_timestamp(ts) - LAG(unix_timestamp(ts))
OVER(ORDER BY id) AS diff FROM ts ORDER BY id);
SELECT COUNT(*) FROM diffs;
COUNT(*)
9
DROP TABLE diffs;
Expect the repeat values in the local timezone and not in epoch
SELECT id, ts, UNIX_TIMESTAMP(ts) FROM ts;
id ts UNIX_TIMESTAMP(ts)
1 2020-10-25 01:40:00 1603582800
2 2020-10-25 02:00:00 1603584000
3 2020-10-25 02:20:00 1603585200
4 2020-10-25 02:40:00 1603586400
5 2020-10-25 02:00:00 1603587600
6 2020-10-25 02:20:00 1603588800
7 2020-10-25 02:40:00 1603590000
8 2020-10-25 03:00:00 1603591200
9 2020-10-25 03:20:00 1603592400
DROP TABLE ts;
|
