By default, time zone names aren’t included with MySQL. The MySQL installation process creates the appropriate tables, but it doesn’t populate these tables.

That said, it’s very easy to populate these tables.

Once populated, we can list out the time zone names available in our system by querying the mysql.time_zone_name table.

Example

Here’s an example of querying the mysql.time_zone_name table for a list of time zone names:

SELECT * 
FROM mysql.time_zone_name
WHERE NAME LIKE 'Australia%';

Result:

+-----------------------+--------------+
| Name                  | Time_zone_id |
+-----------------------+--------------+
| Australia/ACT         |          347 |
| Australia/Adelaide    |          348 |
| Australia/Brisbane    |          349 |
| Australia/Broken_Hill |          350 |
| Australia/Canberra    |          351 |
| Australia/Currie      |          352 |
| Australia/Darwin      |          353 |
| Australia/Eucla       |          354 |
| Australia/Hobart      |          355 |
| Australia/LHI         |          356 |
| Australia/Lindeman    |          357 |
| Australia/Lord_Howe   |          358 |
| Australia/Melbourne   |          359 |
| Australia/North       |          361 |
| Australia/NSW         |          360 |
| Australia/Perth       |          362 |
| Australia/Queensland  |          363 |
| Australia/South       |          364 |
| Australia/Sydney      |          365 |
| Australia/Tasmania    |          366 |
| Australia/Victoria    |          367 |
| Australia/West        |          368 |
| Australia/Yancowinna  |          369 |
+-----------------------+--------------+
23 rows in set (0.00 sec)

On my system, this table contains 598 rows, so I narrowed the query down to just time zone names that begin with Australia.

We can use this table as a reference to find time zone names whenever we need them. For example, we can use the information in the above table to construct the following query:

SELECT CONVERT_TZ(
    '2030-08-25 00:00:00',
    'Australia/Brisbane',
    'Australia/Perth'
    );

Result:

2030-08-24 22:00:00

The CONVERT_TZ() function converts a datetime value from one given time zone to another.

If you find that querying the mysql.time_zone_name table yields no results, it’s likely that nobody has ever populated the time zone tables. In that case, feel free to go ahead and populate them.