Convert local TIME_STAMP to UTC time

Question

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...

Baccate

My system refused EST & UTC, but worked with -05:00 and +00:00. Thanks: CONVERT_TZ('$time_selected_GMT_for_storage','-05:00','+00:00') – Discouragement

@Discouragement You shouldn't do that. Fixed timezone offsets do not always work. Many countries have daylight-savings, which means that the timezone offset will depend on the timestamp itself. Depending if the timestamp is in winter or summer, a different offset must be applied. The reason why your MySQL server refused your command is because it lacks timezone identifiers. You must install them first: dev.mysql.com/downloads/timezones.html Timezone identifiers should be used instead of fixed offsets, because they automatically take care of possible daylight saving times. – Delinquency

Answer 1

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' )

Baccate