[MDEV-13511] MariaDB no longer accepts ISO 8601 with trailing Z Created: 2017-08-13  Updated: 2017-08-14  Resolved: 2017-08-13

Status: Closed
Project: MariaDB Server
Component/s: Time zones
Affects Version/s: 10.2.7
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Peter Sarkozi Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

It works with ADDTIME, but not when inserting.

MariaDB [(none)]> SELECT ADDTIME('2016-10-12T23:00:00Z', '1');
 
+--------------------------------------+
 
| ADDTIME('2016-10-12T23:00:00Z', '1') |
 
+--------------------------------------+
 
| 2016-10-12 23:00:01                  |
 
+--------------------------------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
MariaDB [(none)]> INSERT INTO db.table (start) VALUES ('2016-10-12T23:00:00Z');
 
ERROR 1292 (22007): Incorrect datetime value: '2016-10-12T23:00:00Z' for column 'start' at row 1
 
 
 
MariaDB [(none)]> INSERT INTO db.table (start) VALUES ('2016-10-12T23:00:00');
 
Query OK, 1 row affected (0.00 sec)

Inserting with a trailing Z does work in another db that is 10.1.19. What has changed? It's valid ISO8601 as far as I know.

 Comments   
Comment by Sergei Golubchik [ 2017-08-13 ]

Note "1 warning" after your SELECT ADDTIME statement. If you'd run SHOW WARNINGS (although I, personally, prefer to have

[mysql]
 
show-warnings

in my ~/.my.cnf) you'd see that 'Z' was ignored as some garbage at the end of the datetime value. 10.1 does exactly the same. In 10.2 the default value of sql_mode includes STRICT_TRANS_TABLES, which turns such a warning into an error.

Comment by Peter Sarkozi [ 2017-08-14 ]

Ok, then why does MariaDB not accept a perfectly valid ISO 8601 date?

Comment by Sergei Golubchik [ 2017-08-14 ]

MariaDB does not yet support the "timestamp with a time zone" data type. It is MDEV-10018. And 'Z' means UTC, so when you add 'Z' to a timestamp, it becomes a timestamp with a time zone.

Generated at Thu Feb 08 08:06:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.