?
Solved

converting a datetime value's timezone in a query

Posted on 2006-04-10
6
Medium Priority
?
1,022 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:jm_jackson
  • 3
  • 3
6 Comments
 
LVL 5

Expert Comment

by:tdterry
ID: 16418611

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'


0
 

Author Comment

by:jm_jackson
ID: 16418798
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'
0
 

Author Comment

by:jm_jackson
ID: 16418813
Incidentally, I have MySQL 4.0.25-standard running...
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 5

Accepted Solution

by:
tdterry earned 120 total points
ID: 16419103
Oh, CONVERT_TZ was added in 4.1.3.

You can use FROM_UNIXTIME and UNIX_TIMESTAMP to convert to integers which represent seconds.  There are 3600 seconds in an hour, so all you have to do is add or subtract the correct number of seconds inbetween.  

Note: there may be a loss when converting between UNIX timestamp and back to SQL datetime.  This is documented, but I don't know specifically what cases cause it.  Probably won't a problem unless you were converting back and forth and compounding the conversion multiple times.

For + 5 hours,

SELECT FROM_UNIXTIME( UNIX_TIMESTAMP(click_time), + 18000)
0
 
LVL 5

Expert Comment

by:tdterry
ID: 16419106
oops, typo.  Ignore that extra comma before the +
0
 

Author Comment

by:jm_jackson
ID: 16419296
Brilliant. Thanks tdterry!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month13 days, 20 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question