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!
