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.

It's not an acute bug report, but still it's a hole in mariadb formatting capabilities. This formatting option is available in many programming languages. For example, in PHP we can write `$date->format("T")`.

Why close it like that? it's not like jira is only for acute bugs of existing functionality...

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.

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.

"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