UTC to America/New_York time zone using MySQL

Question

I am storing a date in UTC timezone inside my table but my need is to export a CSV file by converting a date from UTC to America/New_York time zone.

I have tried with CONVERT_TZ MySQL function, but it's giving null.

How I can convert a date from UTC to America/New_York time zone using only MySQL and not using PHP?

score 2 · Answer 1 · answered Dec 14 '16 at 14:17

2

You have to load the timezone table.

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p password

If you do not have superuser access, you can skip friendly timezone names and specify the hours.

CONVERT_TZ(date,'+00:00','-07:00')

answered Dec 14 '16 at 14:17

user3606329

score 1 · Accepted Answer · edited May 23 '17 at 11:53

The reason behind you are getting null result once you are using CONVERT_TZ MySQL function is that TZ time zone table have not been setup.

You can check that time zone table is set up or not. select * from mysql.time_zone; If it's giving null values then you need:

insert time zone in MySql if you want to use CONVERT_TZ MySQL function to convert a date from UTC to America New_Yark time zone.
You can run below query without update time_zone table.

SELECT DATE_SUB( order_date, INTERVAL 5 HOUR ) as OrderDate FROM TABLE_NAME

With date format:

SELECT DATE_FORMAT( DATE_SUB( order_date, INTERVAL 5 HOUR ) , '%Y-%m-%d %h.%i.%s %p' ) as OrderDate FROM TABLE_NAME

Please have a look similar question.

It will help you in "How to insert timezone in MySql".

2 Answers2