When using a function like CONVERT_TZ() to convert between time zones in MySQL, we provide the date/time value, along with the original time zone and the destination time zone (i.e. the time zone that we’re converting to).

One typical way to do this is to use the time zone offset, such as −05:00 to specify the time zone. Another method is to use the time zone name. However, this requires that we have configured named time zones in MySQL.

Example

Here’s an example of using the time zone name when converting between time zones:

SELECT CONVERT_TZ(
    '2035-12-07 12:45:30',
    'Australia/Brisbane',
    'America/New_York'
    );

Result:

2035-12-06 21:45:30

In this case I had already configured named time zones in MySQL.

If I hadn’t already configured named time zones, I would have received a NULL value:

SELECT CONVERT_TZ(
    '2035-12-07 12:45:30',
    'Australia/Brisbane',
    'America/New_York'
    );

Result:

NULL

Configure Named Time Zones

Before we can use the time zone name when converting between time zones, we need to configure MySQL for named time zones.

The MySQL installation process creates a bunch of time zone tables, but it does not populate them with any data. These tables are for holding information about time zone names, and we first need to populate them before we can use named time zones in MySQL.

The reason these tables are empty by default is because it’s usually better that the system handles the time zone, if possible. By leaving these tables empty, MySQL gives us the opportunity to leverage our system’s time zone information.

Fortunately the process of loading these tables is quick and easy. There’s a utility that allows us to do it all with a single line of code.

See How to Set Up Named Time Zones in MySQL for instructions on doing that. Once done, you’ll be able to convert between time zones using the time zone names.