timezone2.result
上传用户:romrleung
上传日期:2022-05-23
资源大小:18897k
文件大小:9k
- 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;
- 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;