"Europe/Amsterdam" is not a location, it's a proper time zone name:

$ TZ=Europe/Amsterdam date

Thu Mar 30 19:04:55 CEST 2023

And it's better than the abbreviation, because

• it is always the same, doesn't change twice a year
• it's unambiguous, while, for example, CDT could mean America/Havana, UTC−04:00 and America/Indiana/Knox, UTC-05:00, see https://en.wikipedia.org/wiki/List_of_tz_database_time_zones or

  $ TZ=America/Havana date

  Thu Mar 30 12:59:05 CDT 2023

  $ TZ=America/Indiana/Knox date

  Thu Mar 30 11:59:11 CDT 2023

Let's not worry too much about semantics. The word time zone is used for different, conflicting concepts. We aren't the first people to get confused by it:

• Europe/Amsterdam, America/Havana, Asia/Jakarta these are locations, some of which have clocks whose offset differs during the year and even some only had that historically.
• UTC+1, UTC-4, UTC-5 these express offsets relative to UTC.
• +01:00, -04:00, -05:00 these are another way to express offsets relative to UTC.
• Central European Time, Central European Summe Time, West African Time are different names for offsets at certain locations in the world. For example, Central European Time has offset +01:00, but so does West African Time.
• CET, CEST, CDT these are abbreviations of the previous category.

To clarify, this is how I used the terms:

• Time zone location: values like Europe/Amsterdam whose offset relative to UTC may change depending on the datetime value.
• Offset: a certain amount of time relative to UTC.
• Time zone: values like Central European Time, which are technically aliases for offsets.
• Time zone abbreviation: values like CET

Normally when a programmer formats a date time value adjusted by the time zone, they will use some kind of time zone location like Europe/Amsterdam. What I am asking for is a function to express a value that includes the time zone abbreviation so that it becomes possible to format a datetime as e.g. '30/03/2023 10:21 CEST'.

It is not a matter of whether Europe/Amsterdam is better than CET/CEST, because they are two different things. I just want to express a datetime's offset to users without writing down a number. This is a common operation in all programming languages that I know.

Ok, as this isn't a bug report, I'm going to close this. But here's a how you can achieve that.

MariaDB [mysql]> select Abbreviation from time_zone_transition join time_zone_transition_type using(Time_zone_id,Transition_type_id) join time_zone_name using (Time_zone_id) where Name='Europe/Amsterdam' and Transition_time < unix_timestamp('2022-01-01 00:00:00') order by Transition_time desc limit 1;

+--------------+

| Abbreviation |

+--------------+

| CET          |

+--------------+

1 row in set (0.003 sec)

MariaDB [mysql]> select Abbreviation from time_zone_transition join time_zone_transition_type using(Time_zone_id,Transition_type_id) join time_zone_name using (Time_zone_id) where Name='Europe/Amsterdam' and Transition_time < unix_timestamp('2022-06-01 00:00:00') order by Transition_time desc limit 1;

+--------------+

| Abbreviation |

+--------------+

| CEST         |

+--------------+

1 row in set (0.001 sec)

MariaDB [mysql]> select Abbreviation from time_zone_transition join time_zone_transition_type using(Time_zone_id,Transition_type_id) join time_zone_name using (Time_zone_id) where Name='Europe/Istanbul' and Transition_time < unix_timestamp('2022-01-01 00:00:00') order by Transition_time desc limit 1;

+--------------+

| Abbreviation |

+--------------+

| +03          |

+--------------+

1 row in set (0.003 sec)

This is not a complete solution because

• it uses the datetime value in the current time zone, you need to wrap it in CONVERT_TZ() for this query to work close to the DST transition times.
• it shows +03 for 2022-01-01 00:00:00 in Europe/Istanbul — nothing I can do here, it's what my /usr/share/zoneinfo contains, no idea why. You can edit time zone tables (in particular, time_zone_transition_type) to have only EET and EEST.

It turns out we're kind of doing it after all. Not quite what you need, but MDEV-31684 will add support for %z and %Z in DATE_FORMAT, so it'll be able to print time zone abbreviations.

Within MDEV-31684 it'll only work for the current time zone (@@time_zone) so not quite what you asked for.

But perhaps we can build on that to make it work for an arbitrary time zone, like DATE_FORMAT(date, format, timezone)?

Anyway, I'll reopen the task.