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()); unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp()) 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()); unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp()) -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()); unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp()) 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'),('2038-01-19 03:14:08'); 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 Warning 1264 Out of range value for column 'ts' at row 6 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 0000-00-00 00:00:00 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'),('2038-01-19 04:14:08'); 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 Warning 1264 Out of range value for column 'ts' at row 6 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 0000-00-00 00:00:00 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'),('2038-01-19 04:44:08'); 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 Warning 1264 Out of range value for column 'ts' at row 6 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 0000-00-00 00:00:00 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 select convert_tz('2038-01-19 04:14:08', 'MET', 'UTC'); convert_tz('2038-01-19 04:14:08', 'MET', 'UTC') 2038-01-19 04:14:08 select convert_tz('2103-01-01 04:00:00', 'MET', 'UTC'); convert_tz('2103-01-01 04:00:00', 'MET', 'UTC') 2103-01-01 04:00:00 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'); convert_tz('2003-12-31 04:00:00', NULL, 'UTC') NULL select convert_tz('2003-12-31 04:00:00', 'SomeNotExistingTimeZone', 'UTC'); convert_tz('2003-12-31 04:00:00', 'SomeNotExistingTimeZone', 'UTC') NULL select convert_tz('2003-12-31 04:00:00', 'MET', 'SomeNotExistingTimeZone'); convert_tz('2003-12-31 04:00:00', 'MET', 'SomeNotExistingTimeZone') NULL select convert_tz('2003-12-31 04:00:00', 'MET', NULL); convert_tz('2003-12-31 04:00:00', 'MET', NULL) NULL select convert_tz( NULL, 'MET', 'UTC'); convert_tz( NULL, 'MET', 'UTC') 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' ); CONVERT_TZ('2001-10-08 00:00:00', MAKE_SET(0,'+01:00'), '+00:00' ) 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(), NOW(); UNIX_TIMESTAMP() NOW() 1288393199 2010-10-30 02:59:59 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=now(); 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(), NOW(); UNIX_TIMESTAMP() NOW() 1288393200 2010-10-30 03:00:00 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=now(); 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` = (current_timestamp()) # # 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(), NOW(); UNIX_TIMESTAMP() NOW() 1288565999 2010-11-01 01:59:59 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=now(); 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` = (current_timestamp()) # # Optimized (24 hours after the DST fall back) # SET timestamp=@first_second_after_dst_fall_back+24*3600; SELECT UNIX_TIMESTAMP(), NOW(); UNIX_TIMESTAMP() NOW() 1288566000 2010-11-01 02:00:00 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=now(); 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(), NOW(); UNIX_TIMESTAMP() NOW() 1301093999 2011-03-26 01:59:59 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=now(); 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(), NOW(); UNIX_TIMESTAMP() NOW() 1301094000 2011-03-26 02:00:00 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=now(); 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` = (current_timestamp()) # # 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(), NOW(); UNIX_TIMESTAMP() NOW() 1301266799 2011-03-28 02:59:59 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=now(); 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` = (current_timestamp()) # # Optimized (24 hours after the DST sprint forward) # SET timestamp=@first_second_after_dst_spring_forward+24*3600; SELECT UNIX_TIMESTAMP(), NOW(); UNIX_TIMESTAMP() NOW() 1301266800 2011-03-28 03:00:00 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=now(); 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(), NOW(); UNIX_TIMESTAMP() NOW() 362707208 1981-06-30 03:59:59 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=now(); 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(), NOW(); UNIX_TIMESTAMP() NOW() 362707209 1981-06-30 04:00:00 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=now(); 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` = (current_timestamp()) # # Not optimized (less than 24 hours after the leap second) # SET timestamp=@leap_second+24*3600-1; SELECT UNIX_TIMESTAMP(), NOW(); UNIX_TIMESTAMP() NOW() 362880008 1981-07-02 03:59:58 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=now(); 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` = (current_timestamp()) # # Not optimized (24 hours after the leap second) # SET timestamp=@leap_second+24*3600; SELECT UNIX_TIMESTAMP(), NOW(); UNIX_TIMESTAMP() NOW() 362880009 1981-07-02 03:59:59 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=now(); 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;