20

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?

Michel
  • 619
  • 4
  • 10
  • 19
  • possible duplicate of [how to set timezone of mysql?](http://stackoverflow.com/questions/930900/how-to-set-timezone-of-mysql) – Evert Oct 09 '11 at 13:34
  • Possible duplicate of [How do I set the time zone of MySQL?](https://stackoverflow.com/questions/930900/how-do-i-set-the-time-zone-of-mysql) – gog May 24 '19 at 09:39

3 Answers3

47
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

Timo Huovinen
  • 53,325
  • 33
  • 152
  • 143
  • this is really the best answer. it doesn't matter what @@session.time_zone happens to be and it allows you to select some columns in UTC and some in session time_zone. Same technique can be used to select as user's time zone, if you have the offset for it. – Garr Godfrey Sep 12 '15 at 03:25
  • NOTE: if session time_zone is subject to daylight saving time adjustments, e.g. CST6CDT, the one hour where the clock "falls back" will be ambiguous. – spencer7593 Jun 28 '16 at 19:25
  • @spencer7593: Why's that? Surely Convert_Tz accounts for daylight savings? – mcmillab Nov 26 '18 at 05:40
  • 3
    @mcmillab: on fallback sunday, there are two overlapping hours that get represented with the same local time. There's an hour from 1AM to 2AM, and then clock gets set back, and then there's another hour, which also gets represented as 1AM to 2AM in local time. As a demonstration, consider: `SELECT CONVERT_TZ('2011-11-06 06:11:00','UTC','US/Central'), CONVERT_TZ('2011-11-06 07:12:00','UTC','US/Central');` and `SELECT CONVERT_TZ('2011-11-06 01:11:00','US/Central','UTC'), CONVERT_TZ('2011-11-06 01:12:00','US/Central','UTC');` – spencer7593 Nov 26 '18 at 15:22
8

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.

fivedigit
  • 18,464
  • 6
  • 54
  • 58
1

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`
drchuck
  • 4,415
  • 3
  • 27
  • 30