drop table if exists t1, t2; 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('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 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 delete from 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 delete from 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 delete from 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:60 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'), ('2037-12-31 23:59:59'),('2038-01-01 00:00:00'); Warnings: Warning 1264 Data truncated; out of range for column 'ts' at row 2 Warning 1264 Data truncated; out of range for column 'ts' at row 3 Warning 1264 Data truncated; out of range 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 2037-12-31 23:59:59 0000-00-00 00:00:00 delete from 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-01 00:59:59'),('2038-01-01 01:00:00'); Warnings: Warning 1264 Data truncated; out of range for column 'ts' at row 2 Warning 1264 Data truncated; out of range for column 'ts' at row 3 Warning 1264 Data truncated; out of range 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-01 00:59:59 0000-00-00 00:00:00 delete from 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-01 01:29:59'),('2038-01-01 01:30:00'); Warnings: Warning 1264 Data truncated; out of range for column 'ts' at row 2 Warning 1264 Data truncated; out of range for column 'ts' at row 3 Warning 1264 Data truncated; out of range 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-01 01:29:59 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-01 00:59:59', 'MET', 'UTC'); convert_tz('2038-01-01 00:59:59', 'MET', 'UTC') 2037-12-31 23:59:59 select convert_tz('2038-01-01 01:00:00', 'MET', 'UTC'); convert_tz('2038-01-01 01:00:00', 'MET', 'UTC') 2038-01-01 01:00:00 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; delete from mysql.user where user like 'mysqltest\_%'; delete from mysql.db where user like 'mysqltest\_%'; delete from mysql.tables_priv where user like 'mysqltest\_%'; delete from mysql.columns_priv where user like 'mysqltest\_%'; flush privileges; create table t1 (a int, b datetime); create table t2 (c int, d datetime); grant all privileges on test.* to mysqltest_1@localhost; show grants for current_user(); Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' GRANT ALL PRIVILEGES ON `test`.* TO 'mysqltest_1'@'localhost' set time_zone= '+00:00'; set time_zone= 'Europe/Moscow'; select convert_tz('2004-10-21 19:00:00', 'Europe/Moscow', 'UTC'); convert_tz('2004-10-21 19:00:00', 'Europe/Moscow', 'UTC') 2004-10-21 15:00:00 select convert_tz(b, 'Europe/Moscow', 'UTC') from t1; convert_tz(b, 'Europe/Moscow', 'UTC') update t1, t2 set t1.b = convert_tz('2004-10-21 19:00:00', 'Europe/Moscow', 'UTC') where t1.a = t2.c and t2.d = (select max(d) from t2); select * from mysql.time_zone_name; ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysql' select Name, convert_tz('2004-10-21 19:00:00', Name, 'UTC') from mysql.time_zone_name; ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysql' delete from mysql.db where user like 'mysqltest\_%'; flush privileges; grant all privileges on test.t1 to mysqltest_1@localhost; grant all privileges on test.t2 to mysqltest_1@localhost; show grants for current_user(); Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' GRANT ALL PRIVILEGES ON `test`.`t2` TO 'mysqltest_1'@'localhost' GRANT ALL PRIVILEGES ON `test`.`t1` TO 'mysqltest_1'@'localhost' set time_zone= '+00:00'; set time_zone= 'Europe/Moscow'; select convert_tz('2004-11-31 12:00:00', 'Europe/Moscow', 'UTC'); convert_tz('2004-11-31 12:00:00', 'Europe/Moscow', 'UTC') 2004-12-01 09:00:00 select convert_tz(b, 'Europe/Moscow', 'UTC') from t1; convert_tz(b, 'Europe/Moscow', 'UTC') update t1, t2 set t1.b = convert_tz('2004-11-30 12:00:00', 'Europe/Moscow', 'UTC') where t1.a = t2.c and t2.d = (select max(d) from t2); select * from mysql.time_zone_name; ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'time_zone_name' select Name, convert_tz('2004-11-30 12:00:00', Name, 'UTC') from mysql.time_zone_name; ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'time_zone_name' delete from mysql.user where user like 'mysqltest\_%'; delete from mysql.db where user like 'mysqltest\_%'; delete from mysql.tables_priv where user like 'mysqltest\_%'; flush privileges; drop table t1, t2;