mysql Query Timezone conversion

Question

I have show times stored in all in Eastern and ID from timezone settings table. Timezone settings table has Timezone_id, GMT offset and the timezone Name.

GMT offset are stored like +6, -4, +3.5, -4.5, etc.

I am trying to write a query to pull the showtime converted to its original timezone. I am doing like this.

SELECT Date_format( CONVERT_TZ( CONVERT_TZ(A.START_TIME,  '+00:00',  '-5:00'),  
'+00:00',     CONCAT(B.GMT_OFFSET , ':00' )), '%Y-%m-%e %r:%i')  
AS  'start_time' from shows A, tz_settings B AND <<JOINS>>;

What I am doing here is first to convert the time from ET to GMT and then applying the GMT offset.

START_TIME is in date format like '2012-4-23 10:15:00' Also, one more problem is converting formats like "3.5" to "3.30" to pass to Convert_TZ

EDIT: Table structure. Main fields.

   shows: 'id', 'show_name', 'stat_time', 'tz_id'
   tz_settings: 'tz_id', 'gmt_offset','tz_name'

Any ideas? Any other functions would help in the mix?

Can you post your table structure to better comprehend what you are doing ? — Zuul, Apr 27 '12 at 18:51
One thing that I can't understand, if you convert from ET to GMT, why do you want to exclude from that conversion the ones in ET ? since you state that all of then are in ET ? (or did I read your question wrong?) — Zuul, Apr 27 '12 at 19:49
You are right. I may have overstated that. Updated my question now. — Kevin Rave, Apr 27 '12 at 19:53

Zuul · Accepted Answer · 2012-04-27T21:20:00.260

1

Don't really know what is the problem that you are having, but I just build 2 MYSQL tables with your structure:

MySQL Tables

TABLE shows ['id', 'show_name', 'start_time', 'tz_id']
TABLE tz_settings ['tz_id', 'gmt_offset','tz_name']

And with this MySQL query:

SELECT Date_format( CONVERT_TZ( CONVERT_TZ( A.START_TIME, '+00:00', '-5:00' ) , '+00:00', CONCAT( B.GMT_OFFSET, ':00' ) ) , '%Y-%m-%e %r:%i' ) AS 'start_time'
FROM shows A, tz_settings B
WHERE A.id =1
AND B.tz_id = A.tz_id

The result was as expected!

Regardless, You should be storing the GMT OFFSET with it's full format to simplify your query thus losing the CONCAT and improving performance. Ex.: instead of +5, store +05:00

Does this helps you in any way ?

EDITED TO INCLUDE MYSQL IF AND REPLACE

SELECT Date_format( CONVERT_TZ( CONVERT_TZ( A.STAT_TIME, '+00:00', '-5:00' ) , '+00:00', if( B.GMT_OFFSET LIKE '%.5', REPLACE( B.GMT_OFFSET, '.5', ':30' ) , CONCAT( B.GMT_OFFSET, ':00' ) ) ) , '%Y-%m-%e %r:%i' ) AS 'start_time'
FROM shows A, tz_settings B
WHERE A.`id` =1
AND B.`tz_id` = A.`tz_id`

So, if the stored GMT OFFSET as .5 on it, it will be replaced by :30, otherwise, it will append the :00 to the existent value. (Tested and returns the value as expected)

edited Apr 27 '12 at 21:20

answered Apr 27 '12 at 20:39

Zuul

16,217
6
61
88

Thats where the problem is. As you can see from my question, the GMT offset is stored as +3.5, +6, +4.5, etc. Fine with +6. But how do we deal with +4.5 (which should be +4:30)? – Kevin Rave Apr 27 '12 at 20:44
But you have no control for the GMT offset stored ? And what do you mean with: 4.5 should be 4:30 ? – Zuul Apr 27 '12 at 20:46
Not sure what you mean by "But you have no control for the GMT offset stored ?". But I cannot change this format now. As for the 2nd question. Convert_TZ takes the 3rd argument in the form of "(+/-)00:00". So I cannot pass 4.5 here, while I can pass +6 as "+6:00". – Kevin Rave Apr 27 '12 at 20:49
Ok, so you cannot store the GMT OFFSET with a proper format, and you need to deal with stored values like "+4.5", using then as "+4:50"! Is that it? (please confirm, before I edit the answer) – Zuul Apr 27 '12 at 20:57
Right. Except "as +4:50" should be " as +4:30". Thanks! – Kevin Rave Apr 27 '12 at 21:03
Check the query after the "EDITED". It now deals with the 30min issue. – Zuul Apr 27 '12 at 21:20
Just starting thinking about he replace command and started working on that. Yours works just fine! Thanks much! – Kevin Rave Apr 27 '12 at 21:34

mysql Query Timezone conversion

1 Answers1