57 lines
No EOL
1.7 KiB
Text
57 lines
No EOL
1.7 KiB
Text
# 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; |