MySQL Bugs: #50658: support numeric time zones (ISO8601)

Bug #50658	support numeric time zones (ISO8601)
Submitted:	27 Jan 2010 12:47	Modified:	5 Feb 2020 15:34
Reporter:	Marc Herbert	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.5.1-m2	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When trying to use a date with a trailing time zone suffix (like for instance '2010-01-27 10:00-01:00'), the time zone is discarded with a warning code 1292. This is a feature request to support the numeric form of trailing time zones. As opposed to zone _names_, this does not look so hard to implement.

The point of the ISO8601 standard is to share dates across the world without error. Not supporting time zones seem to miss the "across the world" part.

How to repeat:
CREATE TABLE a (ts TIMESTAMP);
INSERT INTO a VALUES ('2010-01-27 10:00-01:00');
SHOW WARNINGS;
SELECT * FROM a;

A inconvenient workaround could be to use CONVERT_TZ. But CONVERT_TZ is unfortunately not reliable. See for instance what happens in the CET/CEST time zone:

select @@system_time_zone;
     --   =>   CET
select convert_tz('2005-10-30 00:25:10', '-01:00', 'SYSTEM');
select convert_tz('2005-10-30 00:25:10', '-00:00', 'SYSTEM');

     --     => the last two calls return the same value!

Unlike named time zones, numeric time zones are very reliable because they safely keep away from unpredictable political changes and daylight savings issues. CONVERT_TZ is not reliable because it falls into these traps.

Many thanks for a reasonable feature request.

This was fixed in MySQL 8.0.19, under the heading of

WL#10828 TIMESTAMP/DATETIME VALUES CAN INCLUDE TIMEZONE DETAILS