4

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

fedorqui
  • 275,237
  • 103
  • 548
  • 598
storm_to
  • 1,495
  • 2
  • 16
  • 24
  • 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') – TheSatinKnight Mar 06 '21 at 19:30

1 Answers1

17

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

storm_to
  • 1,495
  • 2
  • 16
  • 24