Node-mysql2: date string parse bug

[tugrul]

const mysql = require('mysql2/promise');

mysql.createConnection({
    host: '127.0.0.1',
    user: 'app',
    password: 'password',
    database: 'app',
}).then((conn) => {
    conn.query('SELECT DATE(?) AS `date_field`', ['2019-08-30']).then(([result]) => {
        console.log(result);
    }).then(() => {
        return conn.close();
    })
});

Date object show 2019-08-29 but date string is 2019-08-30

node version: v10.17.0
mysql2 version: 2.0.2

[tugrul]

dateStrings: true connection option able to use to workaround

[sidorares]

DATATIME (and similar) types in mysql do not include timezone information, js Date you see is probably different because of tz conversion. With dateStrings: true you just get exactly what server sends

[tugrul]

@sidorares full Date object representation is 2019-08-29T21:00:00.000Z

But I tried to evaluate new Date('2019-08-30'); is getting correct value 2019-08-30T00:00:00.000Z

I thought it is doing more than wrap with Date object.

[tugrul]

Also this issue is exists in mysqljs/mysql library.

timezone: 'Z' is solving this issue. I thought adding timezone is correct behavior for DATETIME values but not for DATE values .

[joeyhub]

tugrul, it might be worth listing the TZ of your env and of mysql.

I have to be honest, most people with some experience with timestamps just set everything to UTC to avoid any issues and then TZ handling is only done explicitly on demand.

'SELECT DATE(?) AS date_field', ['2019-08-30']

This should map to:

'SELECT DATE('2019-08-30') AS date_field'

With no conversion to Date because it's a string.

The output however may have a date time so it may be only - on out rather + on in, - on out.

What does this do:

'SELECT DATE(?) AS date_field', [new Date('2019-08-30')]

To be sure there's a command somewhere in the docs just to make the SQL. I think connection.format.

console.log(connection.format('SELECT DATE(?) AS `date_field`', ['2019-08-30']));
console.log(connection.format('SELECT DATE(?) AS `date_field`', [new Date('2019-08-30')]));

Then can run those in mysql directly to see what you get as well (though might be misleading as the client might also do TZ stuff).

Can also experiment with CONCAT("", DATE(?)).

I suspect that won't work either though because it'll probably just to string the param and not apply the connection TZ?

Both read and write date have a timezone param so I guess it's worth a try.

[tugrul]

@joeyhub you don't have to care which kind of handle parameters. because it just simplifying to spot the problem.

Also you can see same problem on following case.

CREATE TABLE `sample` (`draw_date` DATE NOT NULL);
INSERT INTO `sample`(`draw_date`) VALUES('2019-08-30');

then

const mysql = require('mysql2/promise');

mysql.createConnection({
    host: '127.0.0.1',
    user: 'app',
    password: 'password',
    database: 'app',
}).then((conn) => {
    conn.query('SELECT * FROM `sample`').then(([result]) => {
        console.log(result);
    }).then(() => {
        return conn.close();
    })
});

[vincenthawke]

I hope I may hijack this post a bit to share my issue that is basically the same:)
https://stackoverflow.com/questions/59478692/date-mismatch-in-database-when-queried-by-node-script

Thank you for mentioning dateStrings: true connection option, it fixed it for me as well. Insertion always worked fine but reading the date out had this issue.

Did I miss this dateStrings: true connection option in the documentation? Is it posted anywhere? I was not able to find it when looking for something regarding dates or timezones.

[vincenthawke]

@tugrul Where did you find that connection object can accept additional properties like dateStrings? Did you find it online or from implementantion?

[sidorares]

@vincenthawke connection options are documented here: https://github.com/mysqljs/mysql#connection-options

[vincenthawke]

Thank you, I was looking in the wrong place then (only on node-mysql2).

[sidorares]

@vincenthawke this module tries to be mostly compatible with mysqljs/mysql so mostly here in documentation we cover extra features and differences

[sidorares]

But I tried to evaluate new Date('2019-08-30'); is getting correct value 2019-08-30T00:00:00.000Z

the server returns "2019-08-30" string to us, and this library tries it's best to convert that into Date object

if you don't pass tz, it's using "local" timezone - https://github.com/sidorares/node-mysql2/blob/a0ea513b0d4929e6ee61639a09f38a85ac01f7fa/lib/packets/packet.js#L611

> new Date(2019, 7, 30)
2019-08-29T14:00:00.000Z