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

[27 Jan 2010 12:47] Marc Herbert
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;
[27 Jan 2010 12:57] Marc Herbert
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.
[28 Sep 2010 13:51] Susanne Ebrecht
Many thanks for a reasonable feature request.
[5 Feb 2020 15:34] Erlend Dahl
This was fixed in MySQL 8.0.19, under the heading of

WL#10828 TIMESTAMP/DATETIME VALUES CAN INCLUDE TIMEZONE DETAILS