I'm struggling with handling of time values in a MariaDB 10.3. In particular, I can't make sense of when a time value is converted according to a configured time zone and when it isn't.

CREATE TABLE aoeui (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    created DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO aoeui () VALUES ();

SELECT *, CURRENT_TIMESTAMP as ts FROM aoeui;

-- Note: I'm on CEST, which is currently +02:00 and the goal of this value
-- is just to be different from that to illustrate the behaviour.
SET TIME_ZONE = '+08:00';

SELECT *, CURRENT_TIMESTAMP as ts FROM aoeui;

DROP TABLE aoeui;

-- reset timezone, so you can re-run the code in the same session
SET TIME_ZONE = 'SYSTEM';

Note: The insert and the two queries easily run within a second, so any timestamps should differ at most by that short runtime, I'll call them equal here despite this not being 100% true (and a racecondition) in reality.

Note 2: The two values 2023-08-15 14:26:47 +0800 and 2023-08-15 08:26:47 +0200 describe the same point in time, just in two different ways.

In the first SELECT query, the result shows the same point in time twice. In the second query, the result contains the same point in time twice, only represented for a different timezone. However, this second query only gets the CURRENT_TIMESTAMP value correct, the representation of the value retrieved from the table is the same as in the first query.

Example Session

Database changed
MariaDB [tmp]> CREATE TABLE aoeui (
    ->     id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     created DATETIME DEFAULT CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.030 sec)

MariaDB [tmp]> INSERT INTO aoeui () VALUES ();
Query OK, 1 row affected (0.004 sec)

MariaDB [tmp]> SELECT *, CURRENT_TIMESTAMP as ts FROM aoeui;
+----+---------------------+---------------------+
| id | created             | ts                  |
+----+---------------------+---------------------+
|  1 | 2023-08-15 08:26:47 | 2023-08-15 08:26:47 |
+----+---------------------+---------------------+
1 row in set (0.001 sec)

MariaDB [tmp]> SET TIME_ZONE = '+08:00';
Query OK, 0 rows affected (0.000 sec)

MariaDB [tmp]> SELECT *, CURRENT_TIMESTAMP as ts FROM aoeui;
+----+---------------------+---------------------+
| id | created             | ts                  |
+----+---------------------+---------------------+
|  1 | 2023-08-15 08:26:47 | 2023-08-15 14:26:47 |
+----+---------------------+---------------------+
1 row in set (0.000 sec)

MariaDB [tmp]> DROP TABLE aoeui;
Query OK, 0 rows affected (0.010 sec)

You can see in the second query, the hours changed from 08 to 14, but just for one of the results. I would have expected it to change in both.

MariaDB doesn't store the timezone in TIMESTAMP types.

If you change the time_zone within MariaDB subsequent operations will use the new time, but existent data will not be changed.

If you have to work with multiple timezones you should store your time as an UTC timestamp.

CREATE TABLE aoeui (id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
                   created DATETIME DEFAULT UTC_TIMESTAMP());

To display the time depending on your timezone, use CONVERT_TZ() function:

SELECT CONVERT_TZ(created, "+0:00", @@timezone) FROM aoeui;

For more information I would suggest to read the DATETIME documentation or watch Properly Handling Time and Date from Andrew Hutchings.