MySql - SELECT TimeStamp Column in UTC format

Question

In my Mysql 5.0 DB I have a column to control LastUpdated information. The Column is a TimeStamp one and MySql automatic updates data.

I'm trying to select this column in UTC format.

The problem is that the server is setup to US datetime. According to MySql documentation, the DB stores the information in UTC but when I want to display information, it converts it to Server's time.

Is there any way to do SELECT command avoiding this convertion?

Answer 1

SELECT 
CONVERT_TZ(`timestamp_field`, @@session.time_zone, '+00:00') AS `utc_datetime` 
FROM `table_name`

I made a cheatsheet here: Should MySQL have its timezone set to UTC?

To avoid problems like the one mentioned by @spencer7593 with overlapping times, I recommend always storing dates in UTC

Answer 2

Besides changing the default timezone of the server, the timezone can also be adjusted per connection by executing this SQL statement:

SET time_zone = timezone;

Where timezone is the name of the timezone (see MySQL docs).

Alternatively, you can also convert a timestamp to a different timezone using the CONVERT_TZ function.

Answer 3

I needed to alter this to add the "Z" at the end of other things (i.e. like jQuery timeago) knew the time was UTC:

SELECT 
CONCAT(CONVERT_TZ(`timestamp_field`, @@session.time_zone, '+00:00'), 'Z') 
     AS `utc_datetime` 
FROM `table_name`