Support TIMESTAMP with explicit time zone

[mjonss]

Enhancement

In MySQL 8.0.19, the timestamp (or really datetime) literal support explicit time zone, like TIMESTAMP'2024-03-13 11:33:44+01:00'
see https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html

So the suggestion is to allow the "[+-][0-9][0-9]:[0-9][0-9]" syntax for the TIMESTAMP literal, and handling it without any other time zone conversions, apart from adjusting the time accordingly to the given time zone offset.

The reason is to give developers a more straight forward way of inserting timestamps and datetimes without having to change the @@time_zone variable or be affected by internal time zone handling when inserting or querying data.

[dveeden]

Please include CAST(...AT TIME ZONE...) as part of this.

[dveeden]

Another small incompatibility related to this:

mysql-9.0.1> SELECT TIMESTAMP '2024-01-01 18';
+---------------------------+
| TIMESTAMP '2024-01-01 18' |
+---------------------------+
| 2024-01-01 18:00:00       |
+---------------------------+
1 row in set (0.00 sec)

mysql-9.0.1> SELECT TIMESTAMP '2024-01-01 18:00';
+------------------------------+
| TIMESTAMP '2024-01-01 18:00' |
+------------------------------+
| 2024-01-01 18:00:00          |
+------------------------------+
1 row in set (0.00 sec)

mysql-9.0.1> SELECT TIMESTAMP '2024-01-01 18:00:00';
+---------------------------------+
| TIMESTAMP '2024-01-01 18:00:00' |
+---------------------------------+
| 2024-01-01 18:00:00             |
+---------------------------------+
1 row in set (0.00 sec)

tidb> SELECT TIMESTAMP '2024-01-01 18';
ERROR 1292 (22007): Incorrect datetime value: '2024-01-01 18'
tidb> SELECT TIMESTAMP '2024-01-01 18:00';
ERROR 1292 (22007): Incorrect datetime value: '2024-01-01 18:00'
tidb> SELECT TIMESTAMP '2024-01-01 18:00:00';
+---------------------------------+
| TIMESTAMP '2024-01-01 18:00:00' |
+---------------------------------+
| 2024-01-01 18:00:00             |
+---------------------------------+
1 row in set (0.00 sec)

[dveeden]

And for ODBC-style it looks like MySQL is extremely lax with validation, but TiDB isn't.

mysql-9.0.1> SELECT { ts '2024-01' };
+---------+
| 2024-01 |
+---------+
| 2024-01 |
+---------+
1 row in set (0.00 sec)

tidb> SELECT { ts '2024-01' };
ERROR 1292 (22007): Incorrect datetime value: '2024-01'

[dveeden]

And TiDB doesn't raise a warning where MySQL does:

mysql-9.0.1> SELECT DATE'2012@12@31';
+------------------+
| DATE'2012@12@31' |
+------------------+
| 2012-12-31       |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql-9.0.1> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                     |
+---------+------+-------------------------------------------------------------------------------------------------------------+
| Warning | 4095 | Delimiter '@' in position 4 in datetime value '2012@12@31' at row 1 is deprecated. Prefer the standard '-'. |
+---------+------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

tidb> SELECT DATE'2012@12@31';
+------------------+
| DATE'2012@12@31' |
+------------------+
| 2012-12-31       |
+------------------+
1 row in set (0.00 sec)

[dveeden]

For some cases the error number and message are different:

mysql-9.0.1> SELECT TIME '101112';
+---------------+
| TIME '101112' |
+---------------+
| 10:11:12      |
+---------------+
1 row in set (0.00 sec)

mysql-9.0.1> SELECT TIME '109112';
ERROR 1525 (HY000): Incorrect TIME value: '109112'

tidb> SELECT TIME '101112';
+---------------+
| TIME '101112' |
+---------------+
| 10:11:12      |
+---------------+
1 row in set (0.01 sec)

tidb> SELECT TIME '109112';
ERROR 1292 (22007): Truncated incorrect time value: '109112'

[dveeden]

And example for a timestamp literal with a timezone offset:

mysql-9.0.1> SELECT TIMESTAMP '2024-08-07 10:11:12-01:23';
+---------------------------------------+
| TIMESTAMP '2024-08-07 10:11:12-01:23' |
+---------------------------------------+
| 2024-08-07 13:34:12                   |
+---------------------------------------+
1 row in set (0.00 sec)

[dveeden]

Note that the range of offset should be a bit stricter than what's in the description of this issue.

mysql-9.0.1> SELECT TIMESTAMP '2024-08-07 10:11:12-14:00';
+---------------------------------------+
| TIMESTAMP '2024-08-07 10:11:12-14:00' |
+---------------------------------------+
| 2024-08-08 02:11:12                   |
+---------------------------------------+
1 row in set (0.00 sec)

mysql-9.0.1> SELECT TIMESTAMP '2024-08-07 10:11:12-15:00';
ERROR 1525 (HY000): Incorrect DATETIME value: '2024-08-07 10:11:12-15:00'

[dveeden]

So what I think is needed:

• Support timezone offset for literals
• Correct errors to match those of MySQL
• Add warnings for deprecated delimiters
• Relax what ODBC compatible syntax we support
• Relax timestamp parsing for timestamps with only ... HH or ... HH:MM
• Support CAST(...AT TIME ZONE...AS DATETIME)