|
I create this stored function which changes the session time zone:
SET sql_mode=DEFAULT;
|
DELIMITER $$
|
CREATE OR REPLACE FUNCTION f1(tz TEXT) RETURNS TEXT
|
BEGIN
|
SET time_zone=tz;
|
RETURN @@time_zone;
|
END;
|
$$
|
DELIMITER ;
|
SELECT f1('+00:00');
|
Now I execute this anonymous code block
(notice, it changes the session time_zone in the middle of the CREATE..SELECT query by using f1 in the select list):
SET sql_mode=DEFAULT;
|
DELIMITER $$
|
BEGIN NOT ATOMIC
|
DECLARE va TIMESTAMP;
|
SET time_zone='+00:00';
|
SET va= FROM_UNIXTIME(1288477526);
|
SELECT @@time_zone, UNIX_TIMESTAMP(va), va, CONVERT_TZ(va,'+00:00','Europe/Moscow');
|
EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT ? AS a11,f1(''Europe/Moscow'') AS tz12, ? AS a12' USING va, va;
|
SELECT @@time_zone, UNIX_TIMESTAMP(a11), a11, UNIX_TIMESTAMP(a12), a12 FROM t1;
|
END;
|
$$
|
DELIMITER ;
|
It returns these two result sets:
+-------------+--------------------+---------------------+-----------------------------------------+
|
| @@time_zone | UNIX_TIMESTAMP(va) | va | CONVERT_TZ(va,'+00:00','Europe/Moscow') |
|
+-------------+--------------------+---------------------+-----------------------------------------+
|
| +00:00 | 1288477526 | 2010-10-30 22:25:26 | 2010-10-31 02:25:26 |
|
+-------------+--------------------+---------------------+-----------------------------------------+
|
1 row in set (0.00 sec)
|
+---------------+---------------------+---------------------+---------------------+---------------------+
|
| @@time_zone | UNIX_TIMESTAMP(a11) | a11 | UNIX_TIMESTAMP(a12) | a12 |
|
+---------------+---------------------+---------------------+---------------------+---------------------+
|
| Europe/Moscow | 1288477526 | 2010-10-31 02:25:26 | 1288463126 | 2010-10-30 22:25:26 |
|
+---------------+---------------------+---------------------+---------------------+---------------------+
|
1 row in set (0.04 sec)
|
Notice, in the second result set the values in UNIX_TIMESTAMP(a11) and a11 are correct:
- The timestamp is equal to the original timestamp value in va
- The datetime representation is equal to CONVERT_TZ() in the first result set
The values in UNIX_TIMESTAMP(a12) and a12 are wrong:
- The timestamp value 1288463126 is not equal to the original value 1288477526. This is wrong. It should preserve the original value. This is the TIMESTAMP data type!
- The value in a12 should be 2010-10-31 02:25:26, similar to a11. Instead, it preserved the DATETIME representation of va at bind time.
|