[Maria-discuss] Disable validation for `DATETIME` columns
Hi, Is it possible to disable validation for `DATETIME` columns? Context: A customer uses the Europe/Amsterdam session time zone. Therefore, they cannot insert dates during the hour in which DST starts and ends. Using the UTC session time zone would solve the issue. However, they also use auto-converting `TIMESTAMP` columns. Switching to a different session time zone would cause issues with those. Relaxing constraints with `sql_mode=NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES` does not suffice. With kind regards, William Edwards
Hi, William, Could you provide a sequence of SQL commands demonstrating what exactly is not working? I've just tried and I was able to insert '2023-03-26 02:30:00' into a DATETIME column just fine. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org On Mar 30, William Edwards wrote:
Hi,
Is it possible to disable validation for `DATETIME` columns?
Context:
A customer uses the Europe/Amsterdam session time zone. Therefore, they cannot insert dates during the hour in which DST starts and ends.
Using the UTC session time zone would solve the issue. However, they also use auto-converting `TIMESTAMP` columns. Switching to a different session time zone would cause issues with those.
Relaxing constraints with `sql_mode=NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES` does not suffice.
With kind regards,
William Edwards
Hi Sergei, Sergei Golubchik schreef op 2023-03-30 14:54:
Hi, William,
Could you provide a sequence of SQL commands demonstrating what exactly is not working?
I've just tried and I was able to insert '2023-03-26 02:30:00' into a DATETIME column just fine.
After consulting with the customer, it turns out that the error indeed does NOT occur with `DATETIME` columns. However, according to the documentation[1], it should. The issue does occur with `TIMESTAMP` columns. However, this behaviour is not mentioned in the documentation[2]. Am I misunderstanding the documentation? MRE: ``` MariaDB [(none)]> SELECT @@session.time_zone; +---------------------+ | @@session.time_zone | +---------------------+ | SYSTEM | +---------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SELECT @@global.time_zone; +--------------------+ | @@global.time_zone | +--------------------+ | SYSTEM | +--------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SELECT @@system_time_zone; +--------------------+ | @@system_time_zone | +--------------------+ | CET | +--------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> create database timestamp; Query OK, 1 row affected (0.000 sec) MariaDB [(none)]> use timestamp; Database changed MariaDB [timestamp]> create table timestamp (timestamp TIMESTAMP); Query OK, 0 rows affected (0.024 sec) MariaDB [timestamp]> INSERT INTO timestamp VALUES ('2023-03-26 02:30:00'); ERROR 1292 (22007): Incorrect datetime value: '2023-03-26 02:30:00' for column `timestamp`.`timestamp`.`timestamp` at row 1 ``` [1]: https://mariadb.com/kb/en/datetime/#time-zones [2]: https://mariadb.com/kb/en/timestamp
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
On Mar 30, William Edwards wrote:
Hi,
Is it possible to disable validation for `DATETIME` columns?
Context:
A customer uses the Europe/Amsterdam session time zone. Therefore, they cannot insert dates during the hour in which DST starts and ends.
Using the UTC session time zone would solve the issue. However, they also use auto-converting `TIMESTAMP` columns. Switching to a different session time zone would cause issues with those.
Relaxing constraints with `sql_mode=NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES` does not suffice.
With kind regards,
William Edwards
-- With kind regards, William Edwards
Hi, William, On Apr 02, William Edwards wrote:
Hi Sergei,
Sergei Golubchik schreef op 2023-03-30 14:54:
Hi, William,
Could you provide a sequence of SQL commands demonstrating what exactly is not working?
I've just tried and I was able to insert '2023-03-26 02:30:00' into a DATETIME column just fine.
After consulting with the customer, it turns out that the error indeed does NOT occur with `DATETIME` columns. However, according to the documentation[1], it should.
Thanks. The documentation is wrong, we'll fix it.
The issue does occur with `TIMESTAMP` columns. However, this behaviour is not mentioned in the documentation[2].
It is, in a way. If a column uses the TIMESTAMP data type, then any inserted values are converted from the session's time zone to Coordinated Universal Time (UTC) when stored, and converted back to the session's time zone when retrieved So when you use a DATETIME literal that does not correspond to any point in time in UTC, the conversion fails and you get an error. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
participants (2)
-
Sergei Golubchik -
William Edwards