Why does MySql Database save the time wrong?

Question

So I am working on a web application to track the time of employees. It uses node.js with express for the API and a MySql database. The database currently looks like this.

DROP DATABASE IF EXISTS time_tool;
CREATE DATABASE IF NOT EXISTS time_tool;
USE time_tool;

CREATE TABLE employee (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL,
    is_admin BOOLEAN NOT NULL
);

CREATE TABLE time (
    time_id INT PRIMARY KEY AUTO_INCREMENT,
    date DATE NOT NULL,
    arrived TIME,
    left TIME,
    absence_min INT,
    is_sick BOOLEAN NOT NULL,
    employee_id INT NOT NULL,
    
    FOREIGN KEY (employee_id) REFERENCES employee (employee_id)
);

I set up the API with the MySql extension for node.js. When inserting a time into the time table, the API uses the following Method

class Time {
    //time_id is auto incremented by the database and therefore has no initializer in the constructor
    time_id?: number;
    date: string;
    arrived?: string;
    left?: string;
    absence_min?: number;
    is_sick: boolean;
    employee_id: number;

    constructor(date: string, isSick: boolean, employeeID: number, arrived?: string, left?: string, absenceMin?: number) {
        this.date = date;
        this.arrived = arrived;
        this.left = left;
        this.absence_min = absenceMin;
        this.is_sick = isSick;
        this.employee_id = employeeID;
    }

    static CreateTime(time: Time): Promise<number> {
        return new Promise<number>((resolve, reject) => {
            sql.query('INSERT INTO  time SET ?', time, (err: MysqlError | null, res: any): void => {
                if (err) return reject(err);

                resolve(res.insertId);
            });
        });
    }

As I tested the API, I noticed that the time is inserted into the database successfully.

 {
     "id_user": 3,
     "arrived": "2022-04-23T07:25:43.000Z"
 }

Unfortunately the time that the database is returning doesn't match with the inserted time. It's always 2 hours ahead.

{
    "time_id": 1,
    "date": "2022-04-22T22:00:00.000Z",
    "arrived": "09:25:43",
    "left": null,
    "absence_min": null,
    "is_sick": 0,
    "employee_id": 3
}

Since my timezone is UTC+2 (Central European Summer Time) I thought I could fix this problem by setting a default time zone in the my.ini file

[mysqld]
default-time-zone = "+02:00"

Unfortunately that didn't work though. Strangely this issue doesn't appear when I insert a time into the database directly, using MySql Workbench. It only happens with the API.

UPDATE: I found out that it is possible to set the time zone when connecting to the database. My connection looked like this

import mysql, { Connection } from 'mysql';
import DB from './db.config';

const CONN: Connection = mysql.createConnection({
    host: DB.host,
    user: DB.user,
    password: DB.password,
    database: DB.db
});

CONN.connect((err: Error) => {
    if (err) throw err;
    console.log('Successfully connected to the Database');
});

export default CONN;

I attempted to fix the issue by adding the timezone attruibute to the Connection constant.

const CONN: Connection = mysql.createConnection({
    host: DB.host,
    user: DB.user,
    password: DB.password,
    database: DB.db,
    timezone: "+02:00"
});

Though the result is the same.

Answer 1

The problem was the format I used in my POST Request when entering a time. I used the format that the function .toISOString() returned, which includes a Z. The Z stands for UTC+0 timezone. Deleting the Z left me with the correct date inserted into the database.