diff options
Diffstat (limited to 'mysql-test/r/timezone2.result')
| -rw-r--r-- | mysql-test/r/timezone2.result | 246 |
1 files changed, 246 insertions, 0 deletions
diff --git a/mysql-test/r/timezone2.result b/mysql-test/r/timezone2.result new file mode 100644 index 00000000000..5361ff4ffe6 --- /dev/null +++ b/mysql-test/r/timezone2.result @@ -0,0 +1,246 @@ +drop table if exists t1; +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 |