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
|
# This test need to run with TZ=MET/CET, skip if not available
--source include/have_met_timezone.inc
# Bug 32173664 - INSERT WRONG TIMESTAMPS USING EXPLICIT TIME ZONE
# OFFSET IN TIME_ZONE WITH DST
CREATE TABLE ts (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
ts TIMESTAMP);
SELECT @@GLOBAL.TIME_ZONE;
--echo
--echo 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');
--echo
--echo Expect the skip hour between 2:00-3:00
SELECT id, ts, UNIX_TIMESTAMP(ts) FROM ts;
TRUNCATE TABLE ts;
--echo
--echo 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');
--echo Time difference between 2 consecutive rows should be 1200(20 min)
CREATE TABLE diffs (i INT, CONSTRAINT i CHECK (1200 = i) ENFORCED);
--skip_if_hypergraph #hypergraph does not support window functions yet
INSERT INTO diffs (SELECT unix_timestamp(ts) - LAG(unix_timestamp(ts))
OVER(ORDER BY id) AS diff FROM ts ORDER BY id);
--skip_if_hypergraph
SELECT COUNT(*) FROM diffs;
DROP TABLE diffs;
--echo
--echo Expect the repeat values in the local timezone and not in epoch
SELECT id, ts, UNIX_TIMESTAMP(ts) FROM ts;
DROP TABLE ts;
|
