I have a MariaDB running inside a Docker container. There is a table inside a database, that has a column of type TIMESTAMP. I am connecting to the database through my TypeScript application, using mariadb
package. In my application I have a function, that updates the timestamp column in a table. Here is a code sample, how it is implemented:
const now = new Date();
// conn has type PoolConnection
await conn.query("UPDATE my_table SET last_triggered=? WHERE id=?", [
now.toISOString().slice(0, 19).replace("T", " "),
some_id
]);
await conn.commit();
I know that ISO string that I am getting from the now.toISOString()
is UTC(+00:00), meaning has no offset. The problem is that once timestamp is saved in the database, it is saved in the GMT+02:00 timezone, which is UTC+02:00, where the system is located. Once I want to get the timestamp from database, I am getting a timestamp that is two hours earlier than original one, because database converts it to a "correct" UTC by subtracting two hours.
I've checked a couple of sources, and found out that MariaDB is using system time zone settings by default. So I have tried to change it with following SQL commands:
SET GLOBAL time_zone = "+00:00";
and
SET GLOBAL time_zone = "UTC";
and
SET GLOBAL time_zone = "Africa/Accra"; // it is located in GMT+00:00
It takes effect in Global variables, as I've checked it through DBeaver:
But the timestamp I am getting after selecting a record is still returned with two hours difference from original one.
I've also tried to update the timestamp directly in the query:
await conn.query("UPDATE my_table SET timestamp_column=NOW() WHERE id=?", [
some_id
]);
await conn.commit();
but it had no effect.
I'm really confused, what could cause this issue.
The issue lied in the connection configuration of my application. mariadb
client was converting timestamp strings from MariaDB to Date objects, assuming that timestamp is in system's local time, hence two hours difference. So the solution is to set dateStrings: true
while creating a database connection pool:
const pool = mariadb.createPool({host: ..., ..., dateStrings: true});