maria-discuss team mailing list archive

[William Edwards <wedwards@xxxxxxxxxxxxxx>]

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