How to convert timezone in MySQL using the timezone name?

Question

I want to convert date from one timezone to another timezone in MySQL, MySQL query which I am trying is

SELECT DATE_FORMAT(CONVERT_TZ('2015-12-18 06:00:00', '+0:00', '-05:00' ),'%b %d %Y %h:%i %p') AS test
FROM appointment

which is working properly. Now the issue I am having is that my database uses the timezone name as America/New_York.

Is there any way to convert timezone using timezone name not from timezone offset?

Answer 1

To answer the question:

Is there any way to convert timezone using timezone name not from timezone offset

The function CONVERT_TZ has this example and warning:

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

Note To use named time zones such as 'MET' or 'Europe/Moscow', the time zone tables must be properly set up. See Section 10.6, “MySQL Server Time Zone Support”, for instructions.

And the section on Time Zone support has a 'Populating the Time Zone Tables' section which describes the process of setting up the mysql timezone tables.

Answer 2

Finally i got the answer :

The MySQL installation procedure creates a set of time zone tables in the mysql database. On Windows, the tables are part of the preinitialized mysql database. On Unix, the tables are created when mysql_install_db is executed, either automatically during RPM installation or manually if you install from a tar file. However, these time zone tables are created as empty tables, which means that, named time zones can not be used.

To enable named time zones, you must load the tables. This is an optional configuration procedure, but unless it is performed, time zone support is limited to SYSTEM zone and to numeric offsets such as '+06:00'.

On systems that have time zone files, it is best to use them for loading the MySQL time zone tables to ensure that the system and MySQL time zones are based on the same information. Many Unix systems have these files, often located under /usr/share/zoneinfo. For such system, use the mysql_tzinfo_to_sql program to convert the file contents into SQL statements that can be loaded into MySQL by the mysql program. If the files are located at /usr/share/zoneinfo, the command look like this:

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Some systems have no time zone files, such as Windows and HP-UX. For these cases, MySQL provides a distribution a http://dev.mysql.com/downloads/timezones.html that contains a set of populated time zone tables (in form of MyISAM table files). You can download the distribution and install the files in your mysql database to replace the empty tables. Stop the server, copy the files to the mysql database directory, and restart the server.