Bug #111783 DATE_FORMAT function should support time zone abbreviation
Submitted: 17 Jul 2023 18:25 Modified: 18 Jul 2023 13:28
Reporter: BRANDON CHECKETTS Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:8.1 OS:Any
Assigned to: CPU Architecture:Any

[17 Jul 2023 18:25] BRANDON CHECKETTS
Description:
The DATE_FORMAT function currently supports many values in the format string. Surprisingly the Timezone is not one of them, as is common in other database servers and programming languages.

I would like to include '%z' or '%Z' in the format string, and have it rendered as the 3-4 character timezone abbreviation being used to calculate and render the timestamp.

How to repeat:
CREATE TABLE `deleteme` (
  `timestamp` datetime NOT NULL
) ENGINE=InnoDB;

SET time_zone = 'UTC';

-- Note that Daylight Savings in America/Los_Angeles begins on March 13th, 2023,
-- so when I SELECT from these in that timezone, the second value should
-- have Daylight Savings calculated, but not the first.

INSERT INTO `deleteme`
VALUES
    ('2023-03-01 12:34:56'),
    ('2023-04-01 12:34:56');

If I use the convert_tz function, it gets the time values correct. Note that both values were inserted with 12:34:56 UTC, and the value returned from April correctly takes Daylight Savings into consideration.

mysql> SELECT CONVERT_TZ(`timestamp`, 'UTC', 'America/Los_Angeles') FROM `deleteme`;
+-------------------------------------------------------+
| CONVERT_TZ(`timestamp`, 'UTC', 'America/Los_Angeles') |
+-------------------------------------------------------+
| 2023-03-01 04:34:56                                   |
| 2023-04-01 05:34:56                                   |
+-------------------------------------------------------+
2 rows in set (0.00 sec)

However there is not currently a way to see the time zone abbreviation that was applied to each row.

Suggested fix:
The time_zone_transition and time_zone_transition_type tables in the mysql database appear to contain the necessary information to determine which time zone abbreviation to use.

It may be possible to construct a query that joins on these system table to return the time zone abbreviation, but it is extremely cumbersome. I'd like to be able to use a '%z' (or other letter) in the format argument to date_format to include the time zone abbreviation.

mysql> SELECT DATE_FORMAT(
        CONVERT_TZ(`timestamp`, 'UTC', 'America/Los_Angeles'),
        '%Y-%m-%d %H:%i:%s %z'
) AS timestamp_with_timezone
FROM `deleteme`;

+---------------------------+
| timestamp_with_timezone   |
+---------------------------+
| 2023-03-01 04:34:56 PST   |
| 2023-04-01 05:34:56 PDT   |
+---------------------------+
2 rows in set (0.00 sec)

(Currently, since %z is not recognized, it returns the literal letter z)
[18 Jul 2023 13:30] MySQL Verification Team
Hi Mr. Checketts,

Thank you for your feature request.

This is a reasonable request, so we decided to verify it.

Since 8.0 does not get any new features, we are verifying it for the version 8.1.

Verified for 8.1.