Mysql/Mariadb: can you format a date+time to show the time zone at a given location?

Question

I want to get the time zone from a date+time plus location. Some cities in the world exist in different time zones, depending on the time of the year.

For example:

2022-01-01 00:00:00 in Europe/Amsterdam should give CET.
2022-06-01 00:00:00 in Europe/Amsterdam should give CEST.
2022-01-01 00:00:00 in Europe/Istanbul should give EET.

This is trivial to format in programming languages like PHP, but can you also do this in Mysql/MariaDB?

It seems the sql languages don't store time zone information along with date time values, which make this a hard or impossible task. I just want to make sure that I am not missing anything.

Answer 1

A small comment from a long long time ago (over 19 years) in the code:

Now we don't use abbreviations in server but we will do this in future.

What is the case, is the mysql.time_zone_transition_type is already populated with an Abbrevation like "CET".

You'll need to join to the other timezone tables.

I rather crudely did the following:

SELECT UNIX_TIMESTAMP('2022-01-01 00:00:00'),
       Transition_time,
       Abbreviation,
       Is_DST
FROM mysql.time_zone_name tz
JOIN mysql.time_zone_transition USING (Time_zone_id)
JOIN mysql.time_zone_transition_type USING (Time_zone_id,
                                            Transition_type_id)
WHERE tz.Name='Europe/Amsterdam'
  AND Transition_time > UNIX_TIMESTAMP('2022-01-01 00:00:00')
LIMIT 1;

Returning:

+---------------------------------------+-----------------+--------------+--------+
| UNIX_TIMESTAMP('2022-01-01 00:00:00') | Transition_time | Abbreviation | Is_DST |
+---------------------------------------+-----------------+--------------+--------+
|                            1640995200 |      1648342800 | CEST         |      1 |
+---------------------------------------+-----------------+--------------+--------+

However with better attention to the SQL and timezones a more correct answer can be obtained. There's an aspect that the same time can occur twice at the point that DST transition back so that needs to be accounted for.

Why isn't there a simpler mechanism? simply no-one has requested it. I encourage you to do.

Answer 2

Perhaps you are approaching the problem from the wrong direction.

MySQL and MariaDB have a paltry set of tools to help you.

• The "Locale" is a global setting.
• Two (only two) datatypes: DATETIME and TIMESTAMP.

Think of DATETIME as looking at the clock on the wall. Be aware that if your area switches to/from DST, there will be a repeated hour and a skipped hour once a year.

Think of TIMESTAMP as converting the DATETIME to/from UTC for storing.

For an international app, it may be best to do the following:

• Use TIMESTAMPs.
• Require all clients accessing the data to have the Locale set appropriately for their location.

To see the time at a given location other than your own, change the locale dynamically.