Bug #8588 Date parsing dislikes trailing "Z" in ISO-8601 time formats
Submitted: 17 Feb 2005 21:07 Modified: 9 Aug 2005 23:56
Reporter: Jeff Garbers Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.10 OS:Linux (Linux)
Assigned to: Assigned Account CPU Architecture:Any

[17 Feb 2005 21:07] Jeff Garbers
Description:
The ISO-8601 time format allows for a trailing "Z", indicating that the time zone is UTC -- for example, "2005-02-16T18:00:00Z" is 6:00 pm, UTC.  

See http://www.w3.org/TR/NOTE-datetime

When assigning a value or comparing to a DATETIME field, MySQL 3.23 allowed the trailing "Z" without complaint, but 4.1.10 generates a warning and appears to have problems doing comparisons with the parsed value.

Suppressing the "Z" may work around the problem, but I was using it in hopes that it would eliminate (or at least reduce) time-zone ambiguities in date handling.

How to repeat:
CREATE TABLE datetest ( x DATETIME );

-- this generates a warning in 4.1, not in 3.23
INSERT INTO datetest VALUES("2005-02-16T18:00:00Z");

-- in both 4.1 and 3.23, this works and returns one row
SELECT x FROM datetest WHERE x > "2005-02-16T17:00:00";

-- in 3.23 this works and returns one row, 
-- but in 4.1, this generates a warning and returns empty set;
SELECT x FROM datetest WHERE x > "2005-02-16T17:00:00Z";

Suggested fix:
Interpret (or at least ignore) the trailing "Z" in an ISO-8601 formatted date / time string.
[18 Feb 2005 0:29] MySQL Verification Team
It also works on 4.0.XX:

miguel@hegel:~/dbs/4.0$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.24-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE datetest ( x DATETIME );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO datetest VALUES("2005-02-16T18:00:00Z");
Query OK, 1 row affected (0.01 sec)

mysql> SELECT x FROM datetest WHERE x > "2005-02-16T17:00:00Z";
+---------------------+
| x                   |
+---------------------+
| 2005-02-16 18:00:00 |
+---------------------+
1 row in set (0.03 sec)
[3 Jun 2005 1:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25548
[7 Jul 2005 23:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26798
[9 Aug 2005 23:56] Jim Winstead
Now that the related Bug #7308 has been resolved, the basic parsing of ISO-8601 dates is correct, and a warning is merely thrown for the trailing 'Z'. (And since it is not handled as an indication of the time zone, the warning is correct.)

But comparisons against such a date do not work as they did in 4.0. This is already the subject of another bug -- Bug #7557.
[15 Aug 2007 18:14] Hans Ginzel
For 5.0.26-community-nt

INSERT INTO datetest VALUES("2005-02-16T18:00:00Z");

generates error.
[30 Mar 2017 21:42] Rick James
"Me too".  The lack of parsing of ISO-8601 standards continues to pop up in Forum questions.  Here is another:  "GMT timezone in Mysql timestamp literal value" -- http://dba.stackexchange.com/questions/168345/gmt-timezone-in-mysql-timestamp-literal-valu... .  That one is complaining about the failure to parse '2017-08-09 10:11 GMT +07:00'