Bug #58583 UTC_TIMESTAMP inserted in TIMESTAMP column uses time_zone setting

• Submitted: 30 Nov 2010 7:56
• Reporter: Axel Schwenke
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S4 (Feature request)
• Version: 5.1
• OS: Any
• CPU Architecture: Any
• Tags: time zone

[30 Nov 2010 7:56] Axel Schwenke

Description:
According to the manual, values in a TIMESTAMP column are stored in UTC and converted from/to local time on write/read. I.e. if one is in time zone '+01:00' then when writing a to a TIMESTAMP column, one hour is subtracted and on retrieving a value from the column, 1 hour is added.

Unfortunately the same happens when the inserted value is from UTC_TIMESTAMP(). However in that case no conversion must take place.

How to repeat:
mysql> show variables like '%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CET    |
| time_zone        | SYSTEM |
+------------------+--------+

mysql> create table t1 (c1 timestamp); insert into t1 values (localtimestamp()),
 (utc_timestamp()); select * from t1;
+---------------------+
| c1                  |
+---------------------+
| 2010-11-30 08:42:37 |
| 2010-11-30 07:42:37 |
+---------------------+

mysql> set time_zone='+00:00'; select * from t1;
+---------------------+
| c1                  |
+---------------------+
| 2010-11-30 07:42:37 |
| 2010-11-30 06:42:37 |
+---------------------+

Suggested fix:
Looks like internal representation of timestamp variable does not carry a "time zone" tag but only a value. Therefore it's impossible to distinguish the results of LOCALTIMESTAMP() and UTC_TIMESTAMP().

To solve this, either add a "time zone" tag to all related internal data types (just how strings have an "enconding" tag). Or use UTC for internal representation everywhere.

There is slightly related bug #25010