| Bug #96585 | Error Code: 1292. Incorrect datetimevalue for correct dates? | ||
|---|---|---|---|
| Submitted: | 19 Aug 2019 14:21 | Modified: | 19 Aug 2019 23:03 |
| Reporter: | Matthew Roberts | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 8.0.17 | OS: | Debian (10) |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | timestamp, Validation | ||
[19 Aug 2019 14:23]
Matthew Roberts
Adding Timestamp to tags.
[19 Aug 2019 14:24]
Matthew Roberts
Fixing case on tags.
[19 Aug 2019 14:24]
Matthew Roberts
Fixing case on tags.
[19 Aug 2019 14:30]
Matthew Roberts
Have checked and can confirm these are all valid:
INSERT INTO `ProofOfConcept` (`A`) VALUES ('1971-01-01 00:00:00');
INSERT INTO `ProofOfConcept` (`A`) VALUES ('1971-01-01 00:00:01');
INSERT INTO `ProofOfConcept` (`A`) VALUES ('1971-01-01 00:01:00');
INSERT INTO `ProofOfConcept` (`A`) VALUES ('1971-01-01 00:01:01');
INSERT INTO `ProofOfConcept` (`A`) VALUES ('1971-01-01 01:00:00');
[19 Aug 2019 14:34]
Matthew Roberts
According to https://dev.mysql.com/doc/refman/8.0/en/datetime.html : The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. Note the 1 second past, so that would explain why the first is invalid but not the rest.
[19 Aug 2019 15:30]
MySQL Verification Team
Please print your results like below. Thanks:
Your MySQL connection id is 9
Server version: 8.0.18 Source distribution BUILT: 2019-JUL-26
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 8.0 > show variables like "%zone%";
+------------------+-------------------------------+
| Variable_name | Value |
+------------------+-------------------------------+
| system_time_zone | E. South America Standard Tim |
| time_zone | SYSTEM |
+------------------+-------------------------------+
2 rows in set (0.00 sec)
mysql 8.0 > CREATE DATABASE `Testing`;
Query OK, 1 row affected (0.01 sec)
mysql 8.0 > USE `Testing`;
Database changed
mysql 8.0 >
mysql 8.0 > CREATE TABLE `ProofOfConcept` (`A` TIMESTAMP);
Query OK, 0 rows affected (0.02 sec)
mysql 8.0 >
mysql 8.0 > /* BAD? */
mysql 8.0 > INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 00:00:00');
Query OK, 1 row affected (0.01 sec)
mysql 8.0 > INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 00:00:01');
Query OK, 1 row affected (0.00 sec)
mysql 8.0 > INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 00:01:00');
Query OK, 1 row affected (0.00 sec)
mysql 8.0 > INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 00:01:01');
Query OK, 1 row affected (0.00 sec)
mysql 8.0 > INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 01:00:00');
Query OK, 1 row affected (0.00 sec)
mysql 8.0 >
mysql 8.0 > /* Good */
mysql 8.0 > INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 01:01:01');
Query OK, 1 row affected (0.00 sec)
mysql 8.0 > INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 02:00:00');
Query OK, 1 row affected (0.01 sec)
mysql 8.0 > INSERT INTO `ProofOfConcept` (`A`) VALUES ('1971-01-01 00:00:00');
Query OK, 1 row affected (0.01 sec)
mysql 8.0 > INSERT INTO `ProofOfConcept` (`A`) VALUES ('1971-01-01 00:00:01');
Query OK, 1 row affected (0.01 sec)
mysql 8.0 > INSERT INTO `ProofOfConcept` (`A`) VALUES ('1971-01-01 00:01:00');
Query OK, 1 row affected (0.00 sec)
mysql 8.0 > INSERT INTO `ProofOfConcept` (`A`) VALUES ('1971-01-01 00:01:01');
Query OK, 1 row affected (0.00 sec)
mysql 8.0 > INSERT INTO `ProofOfConcept` (`A`) VALUES ('1971-01-01 01:00:00');
Query OK, 1 row affected (0.01 sec)
mysql 8.0 > SELECT * FROM `ProofOfConcept`;
+---------------------+
| A |
+---------------------+
| 1970-01-01 00:00:00 |
| 1970-01-01 00:00:01 |
| 1970-01-01 00:01:00 |
| 1970-01-01 00:01:01 |
| 1970-01-01 01:00:00 |
| 1970-01-01 01:01:01 |
| 1970-01-01 02:00:00 |
| 1971-01-01 00:00:00 |
| 1971-01-01 00:00:01 |
| 1971-01-01 00:01:00 |
| 1971-01-01 00:01:01 |
| 1971-01-01 01:00:00 |
+---------------------+
12 rows in set (0.00 sec)
mysql 8.0 >
[19 Aug 2019 19:54]
Matthew Roberts
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 231
Server version: 8.0.17 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like "%zone%";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | BST |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)
mysql> CREATE DATABASE `Testing`;
Query OK, 1 row affected (0.01 sec)
mysql> USE `Testing`;
Database changed
mysql> CREATE TABLE `ProofOfConcept` (`A` TIMESTAMP);
Query OK, 0 rows affected (0.02 sec)
mysql> /* BAD? */
mysql> INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:00:00' for column 'A' at row 1
mysql> INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 00:00:01');
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:00:01' for column 'A' at row 1
mysql> INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 00:01:00');
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:01:00' for column 'A' at row 1
mysql> INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 00:01:01');
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:01:01' for column 'A' at row 1
mysql> INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 01:00:00');
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 01:00:00' for column 'A' at row 1
mysql>
mysql> /* Good */
mysql> INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 01:01:01');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 02:00:00');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM `ProofOfConcept`;
+---------------------+
| A |
+---------------------+
| 1970-01-01 01:01:01 |
| 1970-01-01 02:00:00 |
+---------------------+
2 rows in set (0.00 sec)
mysql>
[19 Aug 2019 20:08]
Matthew Roberts
Interesting, so it appears the system timezone at record insertion is important even when I specify time literals. So when we're in British Summer Time (GMT+1) the commands will fail, but yet the commands will work when we are back in GMT as the system thinks the time is pre-adjusted to reflect our current time zone.
The 1st of January 1970 would have been GMT (no BST), so the server is not working out the timezone based off the literal entered date/time but instead applying the timezone to the entered date/time because that's the zone we're in now.
mysql> SET time_zone = '+0:00';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:00:00' for column 'A' at row 1
mysql> INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 00:00:01');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 00:01:00');
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 00:01:01');
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 01:00:00');
Query OK, 1 row affected (0.00 sec)
mysql>
As I am entering these dates/times in via a C# program from some UTC/GMT log files I need to ensure I go to insert them with the hour added on so that MySQL can then correct them back into their UTC/GMT form when storing them.
[19 Aug 2019 23:03]
MySQL Verification Team
Thank you for the feedback. Yeah it`s timezone issue.
Description: This has me rather confused but it appears the validation on TIMESTAMP is not correct and my symptoms seem similar to Bug ID #62868. Please note the below dates that are rejected. As these dates are in January and adding a minute and a second seems to make it work I don't think it's timezone related: time_zone SYSTEM I have also removed the default NO_ZERO_DATE and NO_ZERO_IN_DATE variables to no effect. However below should help reproduce the issue. How to repeat: CREATE DATABASE `Testing`; USE `Testing`; CREATE TABLE `ProofOfConcept` (`A` TIMESTAMP); /* BAD? */ INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 00:00:00'); INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 00:00:01'); INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 00:01:00'); INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 00:01:01'); INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 01:00:00'); /* Good */ INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 01:01:01'); INSERT INTO `ProofOfConcept` (`A`) VALUES ('1970-01-01 02:00:00'); Suggested fix: Fix validation on TIMESTAMP :)?