# 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;