drop table if exists t1, t2;
drop function if exists f1;
create table t1 (ts timestamp);
set time_zone='+00:00';
select unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp()) as exp;
exp
0
insert into t1 (ts) values ('2003-03-30 02:30:00');
set time_zone='+10:30';
select unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp()) as exp;
exp
-37800
insert into t1 (ts) values ('2003-03-30 02:30:00');
set time_zone='-10:00';
select unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp()) as exp;
exp
36000
insert into t1 (ts) values ('2003-03-30 02:30:00');
select * from t1;
ts
2003-03-29 16:30:00
2003-03-29 06:00:00
2003-03-30 02:30:00
drop table t1;
select Name from mysql.time_zone_name where Name in 
('UTC','Universal','MET','Europe/Moscow','leap/Europe/Moscow');
Name
Europe/Moscow
leap/Europe/Moscow
MET
Universal
UTC
create table t1 (i int, ts timestamp);
set time_zone='MET';
insert into t1 (i, ts) values
(unix_timestamp('2003-03-01 00:00:00'),'2003-03-01 00:00:00');
insert into t1 (i, ts) values
(unix_timestamp('2003-03-30 01:59:59'),'2003-03-30 01:59:59'),
(unix_timestamp('2003-03-30 02:30:00'),'2003-03-30 02:30:00'),
(unix_timestamp('2003-03-30 03:00:00'),'2003-03-30 03:00:00');
Warnings:
Warning	1299	Invalid TIMESTAMP value in column 'ts' at row 2
insert into t1 (i, ts) values
(unix_timestamp(20030330015959),20030330015959),
(unix_timestamp(20030330023000),20030330023000),
(unix_timestamp(20030330030000),20030330030000);
Warnings:
Warning	1299	Invalid TIMESTAMP value in column 'ts' at row 2
insert into t1 (i, ts) values
(unix_timestamp('2003-05-01 00:00:00'),'2003-05-01 00:00:00');
insert into t1 (i, ts) values
(unix_timestamp('2003-10-26 01:00:00'),'2003-10-26 01:00:00'),
(unix_timestamp('2003-10-26 02:00:00'),'2003-10-26 02:00:00'),
(unix_timestamp('2003-10-26 02:59:59'),'2003-10-26 02:59:59'),
(unix_timestamp('2003-10-26 04:00:00'),'2003-10-26 04:00:00'),
(unix_timestamp('2003-10-26 02:59:59'),'2003-10-26 02:59:59');
set time_zone='UTC';
select * from t1;
i	ts
1046473200	2003-02-28 23:00:00
1048985999	2003-03-30 00:59:59
1048986000	2003-03-30 01:00:00
1048986000	2003-03-30 01:00:00
1048985999	2003-03-30 00:59:59
1048986000	2003-03-30 01:00:00
1048986000	2003-03-30 01:00:00
1051740000	2003-04-30 22:00:00
1067122800	2003-10-25 23:00:00
1067126400	2003-10-26 00:00:00
1067129999	2003-10-26 00:59:59
1067137200	2003-10-26 03:00:00
1067129999	2003-10-26 00:59:59
truncate table t1;
set time_zone='Europe/Moscow';
insert into t1 (i, ts) values
(unix_timestamp('2004-01-01 00:00:00'),'2004-01-01 00:00:00'),
(unix_timestamp('2004-03-28 02:30:00'),'2004-03-28 02:30:00'),
(unix_timestamp('2004-08-01 00:00:00'),'2003-08-01 00:00:00'),
(unix_timestamp('2004-10-31 02:30:00'),'2004-10-31 02:30:00');
Warnings:
Warning	1299	Invalid TIMESTAMP value in column 'ts' at row 2
select * from t1;
i	ts
1072904400	2004-01-01 00:00:00
1080428400	2004-03-28 03:00:00
1091304000	2003-08-01 00:00:00
1099175400	2004-10-31 02:30:00
truncate table t1;
set time_zone='leap/Europe/Moscow';
insert into t1 (i, ts) values
(unix_timestamp('2004-01-01 00:00:00'),'2004-01-01 00:00:00'),
(unix_timestamp('2004-03-28 02:30:00'),'2004-03-28 02:30:00'),
(unix_timestamp('2004-08-01 00:00:00'),'2003-08-01 00:00:00'),
(unix_timestamp('2004-10-31 02:30:00'),'2004-10-31 02:30:00');
Warnings:
Warning	1299	Invalid TIMESTAMP value in column 'ts' at row 2
select * from t1;
i	ts
1072904422	2004-01-01 00:00:00
1080428422	2004-03-28 03:00:00
1091304022	2003-08-01 00:00:00
1099175422	2004-10-31 02:30:00
truncate table t1;
insert into t1 (i, ts) values
(unix_timestamp('1981-07-01 03:59:59'),'1981-07-01 03:59:59'),
(unix_timestamp('1981-07-01 04:00:00'),'1981-07-01 04:00:00');
select * from t1;
i	ts
362793608	1981-07-01 03:59:59
362793610	1981-07-01 04:00:00
select from_unixtime(362793609);
from_unixtime(362793609)
1981-07-01 03:59:59
drop table t1;
create table t1 (ts timestamp);
set time_zone='UTC';
insert into t1 values ('0000-00-00 00:00:00'),('1969-12-31 23:59:59'),
('1970-01-01 00:00:00'),('1970-01-01 00:00:01'),
('2038-01-19 03:14:07');
Warnings:
Warning	1264	Out of range value for column 'ts' at row 2
Warning	1264	Out of range value for column 'ts' at row 3
select * from t1;
ts
0000-00-00 00:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00
1970-01-01 00:00:01
2038-01-19 03:14:07
truncate table t1;
set time_zone='MET';
insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 00:30:00'),
('1970-01-01 01:00:00'),('1970-01-01 01:00:01'),
('2038-01-19 04:14:07');
Warnings:
Warning	1264	Out of range value for column 'ts' at row 2
Warning	1264	Out of range value for column 'ts' at row 3
select * from t1;
ts
0000-00-00 00:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00
1970-01-01 01:00:01
2038-01-19 04:14:07
truncate table t1;
set time_zone='+01:30';
insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 01:00:00'),
('1970-01-01 01:30:00'),('1970-01-01 01:30:01'),
('2038-01-19 04:44:07');
Warnings:
Warning	1264	Out of range value for column 'ts' at row 2
Warning	1264	Out of range value for column 'ts' at row 3
select * from t1;
ts
0000-00-00 00:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00
1970-01-01 01:30:01
2038-01-19 04:44:07
drop table t1;
show variables like 'time_zone';
Variable_name	Value
time_zone	+01:30
set time_zone = default;
show variables like 'time_zone';
Variable_name	Value
time_zone	SYSTEM
set time_zone= '0';
ERROR HY000: Unknown or incorrect time zone: '0'
set time_zone= '0:0';
ERROR HY000: Unknown or incorrect time zone: '0:0'
set time_zone= '-20:00';
ERROR HY000: Unknown or incorrect time zone: '-20:00'
set time_zone= '+20:00';
ERROR HY000: Unknown or incorrect time zone: '+20:00'
set time_zone= 'Some/Unknown/Time/Zone';
ERROR HY000: Unknown or incorrect time zone: 'Some/Unknown/Time/Zone'
select convert_tz(now(),'UTC', 'Universal') = now();
convert_tz(now(),'UTC', 'Universal') = now()
1
select convert_tz(now(),'utc', 'UTC') = now();
convert_tz(now(),'utc', 'UTC') = now()
1
select convert_tz('1917-11-07 12:00:00', 'MET', 'UTC');
convert_tz('1917-11-07 12:00:00', 'MET', 'UTC')
1917-11-07 12:00:00
select convert_tz('1970-01-01 01:00:00', 'MET', 'UTC');
convert_tz('1970-01-01 01:00:00', 'MET', 'UTC')
1970-01-01 01:00:00
select convert_tz('1970-01-01 01:00:01', 'MET', 'UTC');
convert_tz('1970-01-01 01:00:01', 'MET', 'UTC')
1970-01-01 00:00:01
select convert_tz('2003-03-01 00:00:00', 'MET', 'UTC');
convert_tz('2003-03-01 00:00:00', 'MET', 'UTC')
2003-02-28 23:00:00
select convert_tz('2003-03-30 01:59:59', 'MET', 'UTC');
convert_tz('2003-03-30 01:59:59', 'MET', 'UTC')
2003-03-30 00:59:59
select convert_tz('2003-03-30 02:30:00', 'MET', 'UTC');
convert_tz('2003-03-30 02:30:00', 'MET', 'UTC')
2003-03-30 01:00:00
select convert_tz('2003-03-30 03:00:00', 'MET', 'UTC');
convert_tz('2003-03-30 03:00:00', 'MET', 'UTC')
2003-03-30 01:00:00
select convert_tz('2003-05-01 00:00:00', 'MET', 'UTC');
convert_tz('2003-05-01 00:00:00', 'MET', 'UTC')
2003-04-30 22:00:00
select convert_tz('2003-10-26 01:00:00', 'MET', 'UTC');
convert_tz('2003-10-26 01:00:00', 'MET', 'UTC')
2003-10-25 23:00:00
select convert_tz('2003-10-26 02:00:00', 'MET', 'UTC');
convert_tz('2003-10-26 02:00:00', 'MET', 'UTC')
2003-10-26 00:00:00
select convert_tz('2003-10-26 02:59:59', 'MET', 'UTC');
convert_tz('2003-10-26 02:59:59', 'MET', 'UTC')
2003-10-26 00:59:59
select convert_tz('2003-10-26 04:00:00', 'MET', 'UTC');
convert_tz('2003-10-26 04:00:00', 'MET', 'UTC')
2003-10-26 03:00:00
select convert_tz('2038-01-19 04:14:07', 'MET', 'UTC');
convert_tz('2038-01-19 04:14:07', 'MET', 'UTC')
2038-01-19 03:14:07
create table t1 (tz varchar(3));
insert into t1 (tz) values ('MET'), ('UTC');
select tz, convert_tz('2003-12-31 00:00:00',tz,'UTC'), convert_tz('2003-12-31 00:00:00','UTC',tz) from t1 order by tz;
tz	convert_tz('2003-12-31 00:00:00',tz,'UTC')	convert_tz('2003-12-31 00:00:00','UTC',tz)
MET	2003-12-30 23:00:00	2003-12-31 01:00:00
UTC	2003-12-31 00:00:00	2003-12-31 00:00:00
drop table t1;
select convert_tz('2003-12-31 04:00:00', NULL, 'UTC') as exp;
exp
NULL
select convert_tz('2003-12-31 04:00:00', 'SomeNotExistingTimeZone', 'UTC') as exp;
exp
NULL
select convert_tz('2003-12-31 04:00:00', 'MET', 'SomeNotExistingTimeZone') as exp;
exp
NULL
select convert_tz('2003-12-31 04:00:00', 'MET', NULL) as exp;
exp
NULL
select convert_tz( NULL, 'MET', 'UTC') as exp;
exp
NULL
create table t1 (ts timestamp);
set timestamp=1000000000;
insert into t1 (ts) values (now());
select convert_tz(ts, @@time_zone, 'Japan') from t1;
convert_tz(ts, @@time_zone, 'Japan')
2001-09-09 10:46:40
drop table t1;
select convert_tz('2005-01-14 17:00:00', 'UTC', custTimeZone) from (select 'UTC' as custTimeZone) as tmp;
convert_tz('2005-01-14 17:00:00', 'UTC', custTimeZone)
2005-01-14 17:00:00
create table t1 select convert_tz(NULL, NULL, NULL);
select * from t1;
convert_tz(NULL, NULL, NULL)
NULL
drop table t1;
SET @old_log_bin_trust_function_creators = @@global.log_bin_trust_function_creators;
SET GLOBAL log_bin_trust_function_creators = 1;
create table t1 (ldt datetime, udt datetime);
create function f1(i datetime) returns datetime
return convert_tz(i, 'UTC', 'Europe/Moscow');
create trigger t1_bi before insert on t1 for each row
set new.udt:= convert_tz(new.ldt, 'Europe/Moscow', 'UTC');
insert into t1 (ldt) values ('2006-04-19 16:30:00');
select * from t1;
ldt	udt
2006-04-19 16:30:00	2006-04-19 12:30:00
select ldt, f1(udt) as ldt2 from t1;
ldt	ldt2
2006-04-19 16:30:00	2006-04-19 16:30:00
drop table t1;
drop function f1;
SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (t TIMESTAMP);
INSERT INTO t1 VALUES (NULL), (NULL);
LOCK TABLES t1 WRITE;
SELECT CONVERT_TZ(NOW(), 'UTC', 'Europe/Moscow') IS NULL;
CONVERT_TZ(NOW(), 'UTC', 'Europe/Moscow') IS NULL
0
UPDATE t1 SET t = CONVERT_TZ(t, 'UTC', 'Europe/Moscow');
UNLOCK TABLES;
DROP TABLE t1;
#
# Bug #55424: convert_tz crashes when fed invalid data
#
CREATE TABLE t1 (a SET('x') NOT NULL);
INSERT INTO t1 VALUES ('');
SELECT CONVERT_TZ(1, a, 1) FROM t1;
CONVERT_TZ(1, a, 1)
NULL
SELECT CONVERT_TZ(1, 1, a) FROM t1;
CONVERT_TZ(1, 1, a)
NULL
DROP TABLE t1;
End of 5.1 tests
#
# Start of 5.3 tests
#
#
# MDEV-4653 Wrong result for CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5')
#
SET timestamp=unix_timestamp('2001-02-03 10:20:30');
SELECT CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5');
CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5')
2001-02-03 07:05:00
SELECT CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5');
CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5')
2001-02-03 07:05:00
SET timestamp=DEFAULT;
#
# MDEV-5506 safe_mutex: Trying to lock unitialized mutex at safemalloc.c on server shutdown after SELECT with CONVERT_TZ
#
SELECT CONVERT_TZ('2001-10-08 00:00:00', MAKE_SET(0,'+01:00'), '+00:00' ) as exp;
exp
NULL
#
# End of 5.3 tests
#
#
# Start of 10.1 tests
#
#
# MDEV-11895 NO_ZERO_DATE affects timestamp values without any warnings
#
SET sql_mode = '';
CREATE TABLE t1 (a TIMESTAMP NULL) ENGINE = MyISAM;
CREATE TABLE t2 (a TIMESTAMP NULL) ENGINE = MyISAM;
CREATE TABLE t3 (a TIMESTAMP NULL) ENGINE = MyISAM;
SET @@session.time_zone = 'UTC';
INSERT INTO t1 VALUES ('2011-10-29 23:00:00');
INSERT INTO t1 VALUES ('2011-10-29 23:00:01');
INSERT INTO t1 VALUES ('2011-10-29 23:59:59');
SET @@session.time_zone = 'Europe/Moscow';
SET sql_mode='NO_ZERO_DATE';
INSERT INTO t2 SELECT * FROM t1;
SET sql_mode='';
INSERT INTO t3 SELECT * FROM t1;
SELECT UNIX_TIMESTAMP(a), a FROM t2;
UNIX_TIMESTAMP(a)	a
1319929200	2011-10-30 02:00:00
1319929201	2011-10-30 02:00:01
1319932799	2011-10-30 02:59:59
SELECT UNIX_TIMESTAMP(a), a FROM t3;
UNIX_TIMESTAMP(a)	a
1319929200	2011-10-30 02:00:00
1319929201	2011-10-30 02:00:01
1319932799	2011-10-30 02:59:59
DROP TABLE t1, t2, t3;
#
# End of 10.1 tests
#
#
# Start of 10.4 tests
#
#
# MDEV-17203 Move fractional second truncation from Item_xxx_typecast::get_date() to Time and Datetime constructors
# (an addition for the test for MDEV-4653)
SET timestamp=unix_timestamp('2001-02-03 10:20:30');
SET old_mode=ZERO_DATE_TIME_CAST;
Warnings:
Warning	1287	'ZERO_DATE_TIME_CAST' is deprecated and will be removed in a future release
SELECT CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5');
CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5')
NULL
Warnings:
Warning	1292	Truncated incorrect datetime value: '00:00:00'
SELECT CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5');
CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5')
NULL
Warnings:
Warning	1292	Truncated incorrect datetime value: '00:00:00'
SET old_mode=DEFAULT;
SET timestamp=DEFAULT;
#
# MDEV-13995 MAX(timestamp) returns a wrong result near DST change
#
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526)      /*summer time in Moscow*/);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526+3599) /*winter time in Moscow*/);
SET time_zone='Europe/Moscow';
SELECT a, UNIX_TIMESTAMP(a) FROM t1;
a	UNIX_TIMESTAMP(a)
2010-10-31 02:25:26	1288477526
2010-10-31 02:25:25	1288481125
SELECT UNIX_TIMESTAMP(MAX(a)) AS a FROM t1;
a
1288481125
CREATE TABLE t2 (a TIMESTAMP);
INSERT INTO t2 SELECT MAX(a) AS a FROM t1;
SELECT a, UNIX_TIMESTAMP(a) FROM t2;
a	UNIX_TIMESTAMP(a)
2010-10-31 02:25:25	1288481125
DROP TABLE t2;
DROP TABLE t1;
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP);
CREATE TABLE t2 (a TIMESTAMP);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526)      /*summer time in Moscow*/);
INSERT INTO t2 VALUES (FROM_UNIXTIME(1288477526+3599) /*winter time in Moscow*/);
SET time_zone='Europe/Moscow';
SELECT UNIX_TIMESTAMP(t1.a), UNIX_TIMESTAMP(t2.a) FROM t1,t2;
UNIX_TIMESTAMP(t1.a)	UNIX_TIMESTAMP(t2.a)
1288477526	1288481125
SELECT * FROM t1,t2 WHERE t1.a < t2.a;
a	a
2010-10-31 02:25:26	2010-10-31 02:25:25
DROP TABLE t1,t2;
BEGIN NOT ATOMIC
DECLARE a,b TIMESTAMP;
SET time_zone='+00:00';
SET a=FROM_UNIXTIME(1288477526);
SET b=FROM_UNIXTIME(1288481125);
SELECT a < b;
SET time_zone='Europe/Moscow';
SELECT a < b;
END;
$$
a < b
1
a < b
1
CREATE OR REPLACE FUNCTION f1(uts INT) RETURNS TIMESTAMP
BEGIN
DECLARE ts TIMESTAMP;
DECLARE tz VARCHAR(64) DEFAULT @@time_zone;
SET time_zone='+00:00';
SET ts=FROM_UNIXTIME(uts);
SET time_zone=tz;
RETURN ts;
END;
$$
SET time_zone='+00:00';
SELECT f1(1288477526) < f1(1288481125);
f1(1288477526) < f1(1288481125)
1
SET time_zone='Europe/Moscow';
SELECT f1(1288477526) < f1(1288481125);
f1(1288477526) < f1(1288481125)
1
DROP FUNCTION f1;
CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP);
SET time_zone='+00:00';
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526) /*summer time in Mowcow*/,
FROM_UNIXTIME(1288481125) /*winter time in Moscow*/);
SELECT *, LEAST(a,b) FROM t1;
a	b	LEAST(a,b)
2010-10-30 22:25:26	2010-10-30 23:25:25	2010-10-30 22:25:26
SET time_zone='Europe/Moscow';
SELECT *, LEAST(a,b) FROM t1;
a	b	LEAST(a,b)
2010-10-31 02:25:26	2010-10-31 02:25:25	2010-10-31 02:25:26
SELECT UNIX_TIMESTAMP(a), UNIX_TIMESTAMP(b), UNIX_TIMESTAMP(LEAST(a,b)) FROM t1;
UNIX_TIMESTAMP(a)	UNIX_TIMESTAMP(b)	UNIX_TIMESTAMP(LEAST(a,b))
1288477526	1288481125	1288477526
DROP TABLE t1;
CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP,c TIMESTAMP);
SET time_zone='+00:00';
INSERT INTO t1 VALUES (
FROM_UNIXTIME(1288477526) /*summer time in Moscow*/,
FROM_UNIXTIME(1288481125) /*winter time in Moscow*/,
FROM_UNIXTIME(1288481126) /*winter time in Moscow*/);
SELECT b BETWEEN a AND c FROM t1;
b BETWEEN a AND c
1
SET time_zone='Europe/Moscow';
SELECT b BETWEEN a AND c FROM t1;
b BETWEEN a AND c
1
DROP TABLE t1;
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526) /*summer time in Mowcow*/);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288481125) /*winter time in Moscow*/);
SELECT a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a;
a	UNIX_TIMESTAMP(a)
2010-10-30 22:25:26	1288477526
2010-10-30 23:25:25	1288481125
SELECT COALESCE(a) AS a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a;
a	UNIX_TIMESTAMP(a)
2010-10-30 22:25:26	1288477526
2010-10-30 23:25:25	1288481125
SET time_zone='Europe/Moscow';
SELECT a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a;
a	UNIX_TIMESTAMP(a)
2010-10-31 02:25:26	1288477526
2010-10-31 02:25:25	1288481125
SELECT COALESCE(a) AS a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a;
a	UNIX_TIMESTAMP(a)
2010-10-31 02:25:26	1288477526
2010-10-31 02:25:25	1288481125
DROP TABLE t1;
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526) /*summer time in Mowcow*/);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288481126) /*winter time in Moscow*/);
SET time_zone='Europe/Moscow';
SELECT a, UNIX_TIMESTAMP(a) FROM t1 GROUP BY a;
a	UNIX_TIMESTAMP(a)
2010-10-31 02:25:26	1288477526
2010-10-31 02:25:26	1288481126
DROP TABLE t1;
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481126));
SELECT UNIX_TIMESTAMP(a),UNIX_TIMESTAMP(b),CASE a WHEN b THEN 'eq' ELSE 'ne' END AS x FROM t1;
UNIX_TIMESTAMP(a)	UNIX_TIMESTAMP(b)	x
1288477526	1288481126	ne
SET time_zone='Europe/Moscow';
SELECT UNIX_TIMESTAMP(a),UNIX_TIMESTAMP(b),CASE a WHEN b THEN 'eq' ELSE 'ne' END AS x FROM t1;
UNIX_TIMESTAMP(a)	UNIX_TIMESTAMP(b)	x
1288477526	1288481126	ne
DROP TABLE t1;
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP,c TIMESTAMP);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481126),FROM_UNIXTIME(1288481127));
SELECT UNIX_TIMESTAMP(a),UNIX_TIMESTAMP(b),a IN (b,c) AS x FROM t1;
UNIX_TIMESTAMP(a)	UNIX_TIMESTAMP(b)	x
1288477526	1288481126	0
SET time_zone='Europe/Moscow';
SELECT UNIX_TIMESTAMP(a),UNIX_TIMESTAMP(b),a IN (b,c) AS x FROM t1;
UNIX_TIMESTAMP(a)	UNIX_TIMESTAMP(b)	x
1288477526	1288481126	0
DROP TABLE t1;
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481126));
SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1);
a	b
SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1);
a	b
SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1));
a	b
SET time_zone='Europe/Moscow';
SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1);
a	b
SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1);
a	b
SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1));
a	b
DROP TABLE t1;
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP);
INSERT INTO t1 VALUES (FROM_UNIXTIME(1100000000),FROM_UNIXTIME(1200000000));
INSERT INTO t1 VALUES (FROM_UNIXTIME(1100000001),FROM_UNIXTIME(1200000001));
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481126));
INSERT INTO t1 VALUES (FROM_UNIXTIME(1300000000),FROM_UNIXTIME(1400000000));
INSERT INTO t1 VALUES (FROM_UNIXTIME(1300000001),FROM_UNIXTIME(1400000001));
SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1);
a	b
SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1);
a	b
SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1));
a	b
SET time_zone='Europe/Moscow';
SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1);
a	b
SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1);
a	b
SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1));
a	b
DROP TABLE t1;
SET time_zone='Europe/Moscow';
CREATE TABLE t1 (a TIMESTAMP);
CREATE TABLE t2 (a TIMESTAMP);
SET timestamp=1288479599 /*summer time in Mowcow*/;
INSERT INTO t1 VALUES (CURRENT_TIMESTAMP);
SET timestamp=1288479599+3600 /*winter time in Mowcow*/;
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
SELECT t1.a, UNIX_TIMESTAMP(t1.a), t2.a, UNIX_TIMESTAMP(t2.a) FROM t1, t2;
a	UNIX_TIMESTAMP(t1.a)	a	UNIX_TIMESTAMP(t2.a)
2010-10-31 02:59:59	1288479599	2010-10-31 02:59:59	1288483199
SELECT NULLIF(t1.a, t2.a) FROM t1,t2;
NULLIF(t1.a, t2.a)
2010-10-31 02:59:59
DROP TABLE t1, t2;
SET time_zone=DEFAULT;
SET timestamp=DEFAULT;
#
# MDEV-17979 Assertion `0' failed in Item::val_native upon SELECT with timestamp, NULLIF, GROUP BY
#
SET time_zone='+00:00';
CREATE TABLE t1 (a INT, ts TIMESTAMP) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1, FROM_UNIXTIME(1288481126) /*winter time in Moscow*/);
SET time_zone='Europe/Moscow';
CREATE TABLE t2 AS SELECT ts, COALESCE(ts) AS cts FROM t1 GROUP BY cts;
SELECT ts, cts, UNIX_TIMESTAMP(ts) AS uts, UNIX_TIMESTAMP(cts) AS ucts FROM t2;
ts	cts	uts	ucts
2010-10-31 02:25:26	2010-10-31 02:25:26	1288481126	1288481126
DROP TABLE t1,t2;
SET time_zone=DEFAULT;
#
# MDEV-19961 MIN(timestamp_column) returns a wrong result in a GROUP BY query
#
SET time_zone='Europe/Moscow';
CREATE OR REPLACE TABLE t1 (i INT, d TIMESTAMP NOT NULL DEFAULT NOW());
SET timestamp=1288477526  /* this is summer time */ ;
INSERT INTO t1 VALUES (3,NULL);
SET timestamp=1288477526+3599 /* this is winter time*/ ;
INSERT INTO t1 VALUES (3,NULL);
SELECT i, d, UNIX_TIMESTAMP(d) FROM t1 ORDER BY d;
i	d	UNIX_TIMESTAMP(d)
3	2010-10-31 02:25:26	1288477526
3	2010-10-31 02:25:25	1288481125
SELECT i, MIN(d) FROM t1 GROUP BY i;
i	MIN(d)
3	2010-10-31 02:25:26
SELECT i, MAX(d) FROM t1 GROUP BY i;
i	MAX(d)
3	2010-10-31 02:25:25
DROP TABLE t1;
SET timestamp=DEFAULT;
SET time_zone=DEFAULT;
#
# MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE()
#
SET time_zone='Europe/Moscow';
CREATE TABLE t1 (i INT, d TIMESTAMP(6) NOT NULL DEFAULT NOW());
SET timestamp=1288479599.999999  /* this is the last second in summer time */ ;
INSERT INTO t1 VALUES (1,NULL);
SET timestamp=1288479600.000000 /* this is the first second in winter time */ ;
INSERT INTO t1 VALUES (2,NULL);
SELECT i, d, UNIX_TIMESTAMP(d) FROM t1 ORDER BY d;
i	d	UNIX_TIMESTAMP(d)
1	2010-10-31 02:59:59.999999	1288479599.999999
2	2010-10-31 02:00:00.000000	1288479600.000000
CREATE TABLE t2 (i INT, d TIMESTAMP, expected_unix_timestamp INT UNSIGNED);
INSERT INTO t2 SELECT i, ROUND(d) AS d, ROUND(UNIX_TIMESTAMP(d)) FROM t1;
# UNIX_TIMESTAMP(d) and expected_unix_timestamp should return the same value.
# Currently they do not, because ROUND(timestamp) is performed as DATETIME.
# We should fix this eventually.
SELECT i, d, UNIX_TIMESTAMP(d), expected_unix_timestamp FROM t2 ORDER BY i;
i	d	UNIX_TIMESTAMP(d)	expected_unix_timestamp
1	2010-10-31 03:00:00	1288483200	1288479600
2	2010-10-31 02:00:00	1288476000	1288479600
DROP TABLE t2;
DROP TABLE t1;
SET timestamp=DEFAULT;
SET time_zone=DEFAULT;
#
# End of 10.4 tests
#
#
# MDEV-27101 Subquery using the ALL keyword on TIMESTAMP columns produces a wrong result
#
SET time_zone='Europe/Moscow';
CREATE TABLE t1 (a TIMESTAMP NULL);
SET timestamp=1288477526;
/* this is summer time, earlier */
INSERT INTO t1 VALUES (NOW());
SET timestamp=1288477526+3599;
/* this is winter time, later */
INSERT INTO t1 VALUES (NOW());
SELECT a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a;
a	UNIX_TIMESTAMP(a)
2010-10-31 02:25:26	1288477526
2010-10-31 02:25:25	1288481125
SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a <= ALL (SELECT * FROM t1);
a	UNIX_TIMESTAMP(a)
2010-10-31 02:25:26	1288477526
SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a >= ALL (SELECT * FROM t1);
a	UNIX_TIMESTAMP(a)
2010-10-31 02:25:25	1288481125
DROP TABLE t1;
#
# MDEV-32148 Inefficient WHERE timestamp_column=datetime_expr
#
#
# Testing a DST change (fall back)
#
SET time_zone='Europe/Moscow';
SET @first_second_after_dst_fall_back=1288479600;
CREATE TABLE t1 (a TIMESTAMP NULL);
INSERT INTO t1 VALUES ('2001-01-01 10:20:30'),('2001-01-01 10:20:31');
#
# Optimized (more than 24 hours before the DST fall back)
#
SET timestamp=@first_second_after_dst_fall_back-24*3600-1;
SELECT UNIX_TIMESTAMP(), LOCALTIMESTAMP();
UNIX_TIMESTAMP()	LOCALTIMESTAMP()
1288393199	2010-10-30 02:59:59
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=LOCALTIMESTAMP();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP/*WITH LOCAL TIME ZONE*/'2010-10-30 02:59:59'
#
# Not optimized (24 hours before the DST fall back)
#
SET timestamp=@first_second_after_dst_fall_back-24*3600;
SELECT UNIX_TIMESTAMP(), LOCALTIMESTAMP();
UNIX_TIMESTAMP()	LOCALTIMESTAMP()
1288393200	2010-10-30 03:00:00
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=LOCALTIMESTAMP();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = <cache>(localtimestamp())
#
# Not optimized (less than 24 hours after the DST fall back)
#
SET timestamp=@first_second_after_dst_fall_back+24*3600-1;
SELECT UNIX_TIMESTAMP(), LOCALTIMESTAMP();
UNIX_TIMESTAMP()	LOCALTIMESTAMP()
1288565999	2010-11-01 01:59:59
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=LOCALTIMESTAMP();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = <cache>(localtimestamp())
#
# Optimized (24 hours after the DST fall back)
#
SET timestamp=@first_second_after_dst_fall_back+24*3600;
SELECT UNIX_TIMESTAMP(), LOCALTIMESTAMP();
UNIX_TIMESTAMP()	LOCALTIMESTAMP()
1288566000	2010-11-01 02:00:00
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=LOCALTIMESTAMP();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP/*WITH LOCAL TIME ZONE*/'2010-11-01 02:00:00'
DROP TABLE t1;
SET time_zone=DEFAULT;
#
# Testing a DST change (spring forward)
#
SET time_zone='Europe/Moscow';
SET @first_second_after_dst_spring_forward=1301180400;
CREATE TABLE t1 (a TIMESTAMP NULL);
INSERT INTO t1 VALUES ('2001-01-01 10:20:30'),('2001-01-01 10:20:31');
#
# Optimized (more than 24 hours before the DST sprint forward)
#
SET timestamp=@first_second_after_dst_spring_forward-24*3600-1;
SELECT UNIX_TIMESTAMP(), LOCALTIMESTAMP();
UNIX_TIMESTAMP()	LOCALTIMESTAMP()
1301093999	2011-03-26 01:59:59
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=LOCALTIMESTAMP();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP/*WITH LOCAL TIME ZONE*/'2011-03-26 01:59:59'
#
# Not optimized (24 hours before the DST sprint forward)
#
SET timestamp=@first_second_after_dst_spring_forward-24*3600;
SELECT UNIX_TIMESTAMP(), LOCALTIMESTAMP();
UNIX_TIMESTAMP()	LOCALTIMESTAMP()
1301094000	2011-03-26 02:00:00
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=LOCALTIMESTAMP();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = <cache>(localtimestamp())
#
# Not optimized (less than 24 hours after the DST sprint forward)
#
SET timestamp=@first_second_after_dst_spring_forward+24*3600-1;
SELECT UNIX_TIMESTAMP(), LOCALTIMESTAMP();
UNIX_TIMESTAMP()	LOCALTIMESTAMP()
1301266799	2011-03-28 02:59:59
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=LOCALTIMESTAMP();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = <cache>(localtimestamp())
#
# Optimized (24 hours after the DST sprint forward)
#
SET timestamp=@first_second_after_dst_spring_forward+24*3600;
SELECT UNIX_TIMESTAMP(), LOCALTIMESTAMP();
UNIX_TIMESTAMP()	LOCALTIMESTAMP()
1301266800	2011-03-28 03:00:00
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=LOCALTIMESTAMP();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP/*WITH LOCAL TIME ZONE*/'2011-03-28 03:00:00'
DROP TABLE t1;
#
# Testing a leap second
#
SET time_zone='leap/Europe/Moscow';
SET @leap_second=362793609;
/*The 60th leap second*/
CREATE TABLE t1 (a TIMESTAMP);
SET timestamp=@leap_second-1;
INSERT INTO t1 VALUES (NOW());
SET timestamp=@leap_second;
INSERT INTO t1 VALUES (NOW());
SET timestamp=@leap_second+1;
INSERT INTO t1 VALUES (NOW());
SELECT UNIX_TIMESTAMP(a), a FROM t1 ORDER BY UNIX_TIMESTAMP(a);
UNIX_TIMESTAMP(a)	a
362793608	1981-07-01 03:59:59
362793609	1981-07-01 03:59:59
362793610	1981-07-01 04:00:00
INSERT INTO t1 VALUES ('2001-01-01 10:20:30');
#
# Optimized (more than 24 hours before the leap second)
#
SET timestamp=@leap_second-24*3600-1;
SELECT UNIX_TIMESTAMP(), LOCALTIMESTAMP();
UNIX_TIMESTAMP()	LOCALTIMESTAMP()
362707208	1981-06-30 03:59:59
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=LOCALTIMESTAMP();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP/*WITH LOCAL TIME ZONE*/'1981-06-30 03:59:59'
#
# Not optimized (24 hours before the leap second)
#
SET timestamp=@leap_second-24*3600;
SELECT UNIX_TIMESTAMP(), LOCALTIMESTAMP();
UNIX_TIMESTAMP()	LOCALTIMESTAMP()
362707209	1981-06-30 04:00:00
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=LOCALTIMESTAMP();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = <cache>(localtimestamp())
#
# Not optimized (less than 24 hours after the leap second)
#
SET timestamp=@leap_second+24*3600-1;
SELECT UNIX_TIMESTAMP(), LOCALTIMESTAMP();
UNIX_TIMESTAMP()	LOCALTIMESTAMP()
362880008	1981-07-02 03:59:58
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=LOCALTIMESTAMP();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = <cache>(localtimestamp())
#
# Not optimized (24 hours after the leap second)
#
SET timestamp=@leap_second+24*3600;
SELECT UNIX_TIMESTAMP(), LOCALTIMESTAMP();
UNIX_TIMESTAMP()	LOCALTIMESTAMP()
362880009	1981-07-02 03:59:59
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=LOCALTIMESTAMP();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP/*WITH LOCAL TIME ZONE*/'1981-07-02 03:59:59'
DROP TABLE t1;
SET time_zone=DEFAULT;
#
# End of 11.3 tests
#
#
# Start of 11.7 tests
#
#
# MDEV-15751 CURRENT_TIMESTAMP should return a TIMESTAMP [WITH TIME ZONE?]
#
SET time_zone='Europe/Moscow';
CREATE TABLE t1 (a TIMESTAMP);
SET timestamp=1288477526 /*summer time*/;
INSERT INTO t1 VALUES (CURRENT_TIMESTAMP()), (COALESCE(CURRENT_TIMESTAMP()));
SET timestamp=1288477526+3600 /*winter time*/;
INSERT INTO t1 VALUES (CURRENT_TIMESTAMP()), (COALESCE(CURRENT_TIMESTAMP()));
# The two INSERTs produce equal "a" but different UNIX_TIMESTAMP(a)
SELECT a, UNIX_TIMESTAMP(a) FROM t1;
a	UNIX_TIMESTAMP(a)
2010-10-31 02:25:26	1288477526
2010-10-31 02:25:26	1288477526
2010-10-31 02:25:26	1288481126
2010-10-31 02:25:26	1288481126
DROP TABLE t1;
SET time_zone=DEFAULT;
SET time_zone='Europe/Moscow';
CREATE TABLE t1 (a TIMESTAMP);
SET timestamp=1288477526 /*summer time*/;
INSERT INTO t1 VALUES (LOCALTIMESTAMP()), (COALESCE(LOCALTIMESTAMP()));
SET timestamp=1288477526+3600 /*winter time*/;
INSERT INTO t1 VALUES (LOCALTIMESTAMP()), (COALESCE(LOCALTIMESTAMP()));
# The two INSERTs produce equal "a" and equal (summer) UNIX_TIMESTAMP(a)
SELECT a, UNIX_TIMESTAMP(a) FROM t1;
a	UNIX_TIMESTAMP(a)
2010-10-31 02:25:26	1288477526
2010-10-31 02:25:26	1288477526
2010-10-31 02:25:26	1288477526
2010-10-31 02:25:26	1288477526
DROP TABLE t1;
SET time_zone=DEFAULT;
#
# End of 11.7 tests
#