Date mismatch in database when queried by node script

Question

I have a node script that creates a date object, converts it into a string, console logs date and date as string and then inserts date string into a sql database fields of type DATE, DATETIME and TIMESTAMP. The database is on Windows 10 in XAMPP, local time is 2PM.

(async () => {
    const fs = require("fs");
    const mysql = require('mysql2/promise');

    let date = new Date();
    let dateString = date.getFullYear() + "-" + (date.getMonth() + 1) + "-" + date.getDate();
    console.log("date: ", date);
    console.log("datestring: ", dateString);

    const connection = await mysql.createConnection(JSON.parse(fs.readFileSync("../test/databaseLogin.json")));
    await connection.execute("INSERT INTO dates VALUES (?, ?, ?, ?)", [1, dateString, dateString, dateString]);

    const selectResults = await connection.execute("SELECT * FROM `dates` WHERE mainKey = ?", [1]);
    console.log("DB field of type DATE: ", selectResults[0][0].date);
    console.log("DB field of type DATETIME: ", selectResults[0][0].datetime);
    console.log("DB field of type TIMESTAMP: ", selectResults[0][0].timestamp);
    connection.close();
})();

If I query the database from node process with mysql2 npm I get different result that says 24th instead of 25th. Complete console output:

date:  2019-12-25T13:19:01.204Z
datestring:  2019-12-25
DB field of type DATE:  2019-12-24T23:00:00.000Z
DB field of type DATETIME:  2019-12-24T23:00:00.000Z
DB field of type TIMESTAMP:  2019-12-24T23:00:00.000Z

I don’t understand why there is a difference. Maybe some sort of localization issue? But the date was created in same locale as it was read.

Databse representation of data:

MariaDB [test]> SELECT *  FROM dates;
+---------+------------+---------------------+---------------------+
| mainKey | date       | datetime            | timestamp           |
+---------+------------+---------------------+---------------------+
|       1 | 2019-12-25 | 2019-12-25 00:00:00 | 2019-12-25 00:00:00 |
+---------+------------+---------------------+---------------------+

MariaDB [test]> describe dates;
+-----------+-----------+------+-----+-------------------+-----------------------------+
| Field     | Type      | Null | Key | Default           | Extra                       |
+-----------+-----------+------+-----+-------------------+-----------------------------+
| mainKey   | int(11)   | NO   | PRI | NULL              |                             |
| date      | date      | NO   | PRI | NULL              |                             |
| datetime  | datetime  | NO   |     | NULL              |                             |
| timestamp | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------+-----------+------+-----+-------------------+-----------------------------+

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store.

I think mysql2 npm does not support time zone config per connection.

miran80 · Accepted Answer · 2019-12-26 03:50:52Z

10

I found the answer when visiting issues page of the mysql2 npm module.

To get the date string "as is" back form the database, I had to add the following connection settings: dateStrings: true. Full connection object looks now like so:

    const connection = await mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: 'pass',
        database: 'test',
        dateStrings: true
    });

Console output:

date:  2019-12-25T20:38:09.522Z
datestring:  2019-12-25
DB field of type DATE:  2019-12-25
DB field of type DATETIME:  2019-12-25
DB field of type TIMESTAMP:  2019-12-25

edited Dec 26, 2019 at 3:50

answered Dec 25, 2019 at 20:51

miran80

1,1351 gold badge13 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joachim Lous Over a year ago

You can also limit the effect to only DATE: "dateStrings: ['DATE']". This is what you usually want. Representing DATEs with Javascript's grossly misnamed Date type (should have been OffsetDateTime) will only get you into trouble with time zones and daylight saving, but for proper time stamps it makes more sense.

Joachim Lous Over a year ago

No docs, but source says dateStrings?: boolean | Array<'TIMESTAMP' | 'DATETIME' | 'DATE'>;

Collectives™ on Stack Overflow

Date mismatch in database when queried by node script

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related