maria-discuss team mailing list archive

[William Edwards <wedwards@xxxxxxxxxxxxxx>]

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@xxxxxxxxxxx
>
> 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