Skip to content

MySQL Convert Date/Time Timezone in Select Query

by Jon on May 15th, 2013

There are multiple ways to convert timezones when writing code either doing it through a programming language like PHP or if working with a database doing it in a query. Here is an example of how to make a timezone conversion using MySQL.

This MySQL query converts a UTC timestamp to Eastern time.

select sid, DATE_FORMAT(CONVERT_TZ(str_to_date(stamp,'%Y%m%d%H%i%s'),
'UTC','US/Eastern'), '%m/%d/%y %h:%i %p') as 'Date - Eastern' 
from SNATEST.myspeed where sid = 'test_95662/3382' limit 10;

As you can see I used the CONVERT_TZ MySQL function that will take a date/time from one specified timezone to another timezone.

The syntax is:

CONVERT_TZ('date/time','starting timezone','ending timezone')

Below is what the SQL output looks like.

mysql> select sid, DATE_FORMAT(CONVERT_TZ(str_to_date(stamp,'%Y%m%d%H%i%s'),'UTC','US/Eastern'), '%m/%d/%y %h:%i %p') as 'Date - Eastern' from SNATEST.myspeed where sid = 'test_95662/3382' limit 10;
+-----------------+-------------------+
| sid             | Date - Eastern    |
+-----------------+-------------------+
| test_95662/3382 | 11/14/13 09:33 AM |
| test_95662/3382 | 11/14/13 09:34 AM |
| test_95662/3382 | 11/14/13 09:40 AM |
| test_95662/3382 | 11/14/13 09:41 AM |
| test_95662/3382 | 11/14/13 09:46 AM |
| test_95662/3382 | 11/14/13 09:47 AM |
| test_95662/3382 | 11/14/13 09:52 AM |
| test_95662/3382 | 11/14/13 09:53 AM |
| test_95662/3382 | 11/14/13 09:59 AM |
| test_95662/3382 | 11/14/13 09:59 AM |
+-----------------+-------------------+
10 rows in set (0.03 sec)

From → Linux, Tech

No comments yet

Leave a Reply

Note: XHTML is allowed. Your email address will never be published.

Subscribe to this comment feed via RSS

%d bloggers like this: