I just realised that I forgot to do `set time_zone` again... I think 'it's Saturday' will pass as an excuse. Sorry for the noise. William Edwards schreef op 2021-11-13 15:06:
Hi,
I have a `timestamp` column, because I would like to insert timestamps in UTC. The documentation says that the inserted value should be converted from my session's time zone to UTC, but I can't reproduce that behaviour. I'm on MariaDB 10.5.9.
I have a column of type `timestamp`:
`created_at` timestamp NULL DEFAULT NULL
The documentation at https://mariadb.com/kb/en/timestamp/ says:
"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."
However, I cannot reproduce this intended behaviour. I set my time zone as follows. (The value was 'SYSTEM', which should also be '+01:00', but I'm setting it explicitly just in case.)
-- MariaDB [db]> set time_zone = '+01:00'; Query OK, 0 rows affected (0.006 sec)
MariaDB [db]> select now(); +---------------------+ | now() | +---------------------+ | 2021-11-13 14:56:09 | +---------------------+ 1 row in set (0.001 sec)
MariaDB [db]> SELECT @@global.time_zone, @@session.time_zone; +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | SYSTEM | +01:00 | +--------------------+---------------------+ 1 row in set (0.000 sec) --
Then, I update the values in the `timestamp` column to the timestamp of my session's time zone:
-- MariaDB [db]> update objs set created_at=now(); Query OK, 2 rows affected (0.001 sec) Rows matched: 2 Changed: 2 Warnings: 0 --
... I would expect the inserted value to be converted from my session's time zone ('+01:00') to UTC. However, the inserted value is actually a timestamp of my session's time zone:
-- MariaDB [db]> select created_at from objs; +---------------------+ | created_at | +---------------------+ | 2021-11-13 14:56:42 | | 2021-11-13 14:56:42 | +---------------------+ 2 rows in set (0.000 sec) --
I would not be surprised if I'm looking over something very obvious, but I haven't figured it out yet. Hopefully someone sees what I'm missing.
TIA!
-- With kind regards, William Edwards