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)