datetime fields returned as date when time is 00:00:00

[lirancr]

Setting a DATETIME field with the value 2019-10-01 00:00:00
Then retrieve the data using the execute command:
const [result] = connection.execute('SELECT * FROM my_table', [])

result will equal to '2019-10-01' instead of '2019-10-01 00:00:00' although it's a DATETIME field and not merely a DATE

note that when using query the behavior is as expected

[sidorares]

could you post simple self contained example, including schema creation and initial data insert?

[lirancr]

Sure here is a standalone snippet

    //running with node 10.16.0 using mysql2 ^1.7.0

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

    async function main() {
    let db = await mysql.createConnection({
        host: 'localhost',
        port: '3306',
        user: 'root',
        password: 'root'
    });
    await db.query("SET time_zone='+00:00'");
    await db.query('DROP DATABASE IF EXISTS test_db');
    await db.query('CREATE DATABASE IF NOT EXISTS test_db');
    await db.close();

    db = await mysql.createConnection({
        host: 'localhost',
        port: '3306',
        user: 'root',
        password: 'root',
        database: 'test_db',
        dateStrings: true,
        timezone: '+00:00'
    });

    await db.query('CREATE TABLE IF NOT EXISTS mytbl (id INT AUTO_INCREMENT, created DATETIME NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB');
    await db.execute('INSERT INTO mytbl (created) VALUES (?)',['2019-10-01 00:00:00']);
    let [result] = await db.execute('SELECT * FROM mytbl', []);
    console.log('execute result',result[0]);
    [result] = await db.query('SELECT * FROM mytbl', []);
    console.log('query result',result[0]);
    
    }

    main();

scratches.zip

[sidorares]

Data in db has DATETIME type, but actual value only contain 4 bytes ( 2 for year, 1 for month and 1 for day ) - this is where it gets deserialized to a string ( note that there is no intermediate conversion to JS Date when you pass dateStrings: true option:

node-mysql2/lib/packets/packet.js

Lines 303 to 306 in aa0f727

	y = this.readInt16();
	m = this.readInt8();
	d = this.readInt8();
	str = [leftPad(4, y), leftPad(2, m), leftPad(2, d)].join('-');

I agree, result of (await db.execute|query('SELECT * FROM mytbl')[0][0]).toString() should be all the same for dateStrings: true and dateStrings: false

I guess the fix should be to append 00:00:00 when it's just 4 bytes of datetime returned

Do you want to volunteer to fix this @lirancr ?

[lirancr]

Of course. Any reason why query behaves differently ?

[sidorares]

Any reason why query behaves differently ?

execute and query serialise responses very differently, for query results come as text and for execute - binary encoded

In the above example when dateStrings is set to true for query we return just whatever was in query response and with execute we construct date as string from binary data we have ( 4 bytes ). When dateStrings is not set in both execute and query we construct js Date object

[lirancr]

Done, see associated PR

[sidorares]

Travis CI tests fail for some reason, I'll have a look tomorrow

[lirancr]

any update on this ?
let me know if there's something i do to help out

[deko2369]

hi, when will #1147 be merged?