Inaccurate ranges for DATETIME and TIMESTAMP

[coffeegoddd]

Dolt version 0.51.12.
MySQL version 8.0.31

Dolt does not allow the same TIMESTAMP and DATETIME range as MySQL. From MySQL, the ranges should be inclusive of the start of the range:

mysql> create table t1 (pk int primary key, ca datetime not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 (pk,ca) values (1, '0001-01-01 00:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 (pk,ca) values (2, '0000-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+---------------------+
| pk | ca                  |
+----+---------------------+
|  1 | 0001-01-01 00:00:00 |
|  2 | 0000-01-01 00:00:00 |
+----+---------------------+
2 rows in set (0.00 sec)

There also might be a bug/typo in MySQL since it's docs say that the DATETIME range starts at 1000-01-01 00:00:00, but can start well before that.

mysql> create table t2 (pk int primary key, ca timestamp not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 (pk,ca) values (1, '1970-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+----+---------------------+
| pk | ca                  |
+----+---------------------+
|  1 | 1970-01-01 00:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

TIMESTAMP inserts do not need to be one second after the epoch in MySQL, which Dolt requires.

dolt sql -q 'insert into t1 (pk,ca) values (3, "0001-01-01 00:00:00");'
error on line 1 for query insert into t1 (pk,ca) values (3, "0001-01-01 00:00:00"): value "0001-01-01 00:00:00" is outside of datetime range
value "0001-01-01 00:00:00" is outside of datetime range

dolt sql -q 'insert into t2 (pk,ca) values (3, "1970-01-01 00:00:00");'
error on line 1 for query insert into t2 (pk,ca) values (3, "1970-01-01 00:00:00"): value "1970-01-01 00:00:00" is outside of timestamp range
value "1970-01-01 00:00:00" is outside of timestamp range

[andy-wm-arthur]

related: #4753

[Hydrocharged]

Regarding the TIMESTAMP issue, this is actually a time zone issue.

SELECT CONVERT_TZ("1970-01-01T00:00:01Z", "+00:00", @@session.time_zone);

Returns 1969-12-31 16:00:01, which is valid for me as my time zone is GMT-8. So inputting 1969-12-31 16:00:01 actually works, as internally it's stored in UTC (or GMT+0) and then converted back. You can actually verify this behavior by changing your time zone to UTC using:

SET @@session.time_zone = '+00:00';

And now the table returns 1970-01-01 00:00:01. With the time zone still in UTC, trying to use 1970-01-01 00:00:00 results in an error, so the minimum value is still one second over. To explain the discrepancy, GMS (and Dolt by extension) doesn't support session time zones right now, so all times are in UTC. This is also not helped by the fact that no time zone information is reported by MySQL natively, so it's hard to know that timezones are even affected by this. After all, what you put in is what you get out, so it's not something that's easily noticeable.

I'm still investigating DATETIME.

[Hydrocharged]

As far as I can tell, the docs are just wrong about DATETIME, and the minimum year value is below 1000, even in the strictest modes that MySQL allows. We should definitely note this in the datetimetype.go file.

[Hydrocharged]

PR regarding this issue: dolthub/go-mysql-server#1490