Bug #25010 There is no way to display TIMESTAMP values in UTC without setting variables
Submitted: 12 Dec 2006 14:34
Reporter: Victoria Reznichenko Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:5.0 OS:Any (any)
Assigned to: CPU Architecture:Any

[12 Dec 2006 14:34] Victoria Reznichenko
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.