asked on

converting a datetime value's timezone in a query

Hi,

I've got a simple query "SELECT * FROM table1" and one of the columns in table1 holds a datetime value. I want to convert this datetime value to a different time zone (presumably using CONVERT_TZ??). Can someone help me get the correct syntax for using this function within an SQL SELECT query...

Thanks,

JJ

tdterry

select convert_tz(my_time_field, 'DB time zone', 'desired time zone') from table1;

You will of course need to know the time zone that the data is stored in and the time zone of the user to whom your displaying the data.

From the manual:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
-> '2004-01-01 13:00:00'
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
-> '2004-01-01 22:00:00'

jm_jackson

ASKER

Thanks,

I'm running into a problem as I get an error message when trying to execte the following query:

SELECT convert_tz(click_time, '+00:00','+05:00') FROM clicks

(where click_time is the datetime column)

The error I get is:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( click_time , '+00:00' , '+05:00' ) FROM table1'

jm_jackson

ASKER

Incidentally, I have MySQL 4.0.25-standard running...

ASKER CERTIFIED SOLUTION

tdterry

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tdterry

oops, typo. Ignore that extra comma before the +

jm_jackson

ASKER

Brilliant. Thanks tdterry!