Bug #25010 There is no way to display TIMESTAMP values in UTC without setting variables

• Submitted: 12 Dec 2006 14:34
• Reporter: Victoria Reznichenko
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S4 (Feature request)
• Version: 5.0
• OS: Any (any)
• CPU Architecture: Any

[12 Dec 2006 14:34] Victoria Reznichenko

Description:
Currently if you want to retrieve TIMESTAMP data in UTC you can do it only with setting  time_zone variable on connection basis:

mysql> drop table if exists tstest;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> create table tstest(ts timestamp default current_timestamp);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into tstest values ();
Query OK, 1 row affected (0.00 sec)

mysql> select ts from tstest;
+---------------------+
| ts                  |
+---------------------+
| 2006-12-12 17:30:55 |
+---------------------+
1 row in set (0.00 sec)

mysql> SET time_zone = '+00:00';
Query OK, 0 rows affected (0.04 sec)

mysql> select ts from tstest;
+---------------------+
| ts                  |
+---------------------+
| 2006-12-12 14:30:55 |
+---------------------+
1 row in set (0.00 sec)

It would be good to have function that returns values in UTC and doesn't depend on connection settings.

How to repeat:
see above

Suggested fix:
It would be good if CONVERT_TZ() works with TIMESTAMP values + UTC or UTC_TIMESTAMP() accepts parameters.
See also comments below.

[5 Oct 2008 10:52] Konstantin Osipov

Setting to Server: Data Types because I think the solution is to have TIMESTAMP WITH TIME ZONE data type.