Convert local TIME_STAMP to UTC time

mysql convert-tz
storm_to picture storm_to · Jul 22, 2011 · Viewed 15.5k times · Source

I have two tables with time stamps

TABLE1 with TIME_STAMP in local time

TABLE2 with TIME_STAMP in UTC

I need to do something like

    select count(*) from TABLE1 where TIME_STAMP > TABLE2.TIME_STAMP

The problem is that this app will be deployed in multiple time zones so I cannot use the following

    SELECT CONVERT_TZ(TABLE2.TIME_STAMP,'EST','UTC')

because 'EST' can be any time zone.

Is there a way of getting local time zone code and substituting it as second argument? Or is there a better and more direct approach to solving my issue?

NOTE: Both TABLE1 and TABLE2 TIME_STAMPS have to remain as local and UTC respectively as they are externals that I have no control over...

Answer

storm_to picture storm_to · Feb 14, 2012

Here is the solution I implemented:

This will convert NOW from local timezone to UTC
SELECT CONVERT_TZ( NOW(), @@session.time_zone, '+00:00' )

This will convert any given TIMESTAMP string from local timezone to UTC
SELECT CONVERT_TZ( "2012-02-14 16:44:36", @@session.time_zone, '+00:00' )

Related questions

How to Convert UTC Date To Local time Zone in MySql Select Query

I am using this Where Condition in One Of my query with MySql Database.My Problem is that i have one displaytime column in my table but that table column shows the data in UTC Time.and i want to …

Read More
Can MySQL convert a stored UTC time to local timezone?

Can MySQL convert a stored UTC time to local time-zoned time directly in a normal select statement? Let's say you have some data with a timestamp (UTC). CREATE TABLE `SomeDateTable` ( `id` int(11) NOT NULL auto_increment, `value` float NOT NULL …

Read More
MySQL CONVERT_TZ()

I am trying to set up a database that stores daily alert times as specified by users. For example, the user wants to receive an alert if some criterion is met each day between 7:00 AM and 7:30 AM. In trying to …

Read More