1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
#
# This file contains tests for the AT TIME ZONE operator.
#
SET time_zone = '+01:00';
SELECT cast(TIMESTAMP'2019-10-10 10:11:12' AT TIME ZONE 'UTC' AS DATETIME);
cast(TIMESTAMP'2019-10-10 10:11:12' AT TIME ZONE 'UTC' AS DATETIME)
2019-10-10 09:11:12
SELECT cast(TIMESTAMP'2019-10-10 10:11:12' AT TIME ZONE '+00:00' AS DATETIME);
cast(TIMESTAMP'2019-10-10 10:11:12' AT TIME ZONE '+00:00' AS DATETIME)
2019-10-10 09:11:12
SELECT cast(TIMESTAMP'2019-10-10 10:11:12+00:00' AT TIME ZONE '+00:00' AS DATETIME);
cast(TIMESTAMP'2019-10-10 10:11:12+00:00' AT TIME ZONE '+00:00' AS DATETIME)
2019-10-10 10:11:12
# Casting from invalid types.
SELECT cast( TIME'10:10' AT TIME ZONE 'UTC' AS DATETIME );
ERROR HY000: Cannot cast value to TIMESTAMP WITH TIME ZONE.
SELECT cast( '2019-10-10' AT TIME ZONE 'UTC' AS DATETIME );
ERROR HY000: Cannot cast value to TIMESTAMP WITH TIME ZONE.
SELECT cast( 123 AT TIME ZONE 'UTC' AS DATETIME );
ERROR HY000: Cannot cast value to TIMESTAMP WITH TIME ZONE.
# Table data
CREATE TABLE t1 ( a TIMESTAMP, b DATETIME );
INSERT INTO t1 VALUES ( '2019-10-10 10:11:12+00:00', '2019-10-10 10:11:12+00:00' );
SELECT * FROM t1;
a b
2019-10-10 11:11:12 2019-10-10 11:11:12
SELECT cast( a AT TIME ZONE '+00:00' AS DATETIME ) FROM t1;
cast( a AT TIME ZONE '+00:00' AS DATETIME )
2019-10-10 10:11:12
SELECT cast( b AT TIME ZONE '+00:00' AS DATETIME ) FROM t1;
ERROR HY000: Cannot cast value to TIMESTAMP WITH TIME ZONE.
SET time_zone = '+12:34';
SELECT cast( a AT TIME ZONE '+00:00' AS DATETIME ) FROM t1;
cast( a AT TIME ZONE '+00:00' AS DATETIME )
2019-10-10 10:11:12
SELECT cast( b AT TIME ZONE '+00:00' AS DATETIME ) FROM t1;
ERROR HY000: Cannot cast value to TIMESTAMP WITH TIME ZONE.
DROP TABLE t1;
SELECT cast( '2019-10-10 10:11' AT TIME ZONE 'UTC' AS DATETIME );
ERROR HY000: Cannot cast value to TIMESTAMP WITH TIME ZONE.
SET time_zone = DEFAULT;
#
# Bug#31405594: WL12535: BACKTRACE:ITEM_FUNC_AT_TIME_ZONE |
# SELECT_LEX_UNIT::EXECUTEITERATORQUER
#
RENAME TABLE mysql.time_zone TO time_zone_backup;
CREATE TABLE t1 ( a TIMESTAMP );
DROP TABLE t1;
RENAME TABLE time_zone_backup TO mysql.time_zone;
|
