Mysql datetime different in Javascript

Question

I post here today because I encounter a problem storing a Datetime in mysql with Javascript. The datetime I store, and I can see in Phpmyadmin is not the same as the datetime I got with a query (Select)

I think this is a timezone related problem, but I don't know how to solve this. I tried to save time in UTC with

UTC_TIMESTAMP()

But I still don't get the right datetime.

I store the datetime with :

connection.query("UPDATE `users` SET `last_disconnect` = now() WHERE `users`.`uuid` = ?", [userId])

(I tried to create a new Javascript date and store it but it's the same)

Here is the datetime stored (the datetime I can see in Phpmyadmin) :

2019-06-04 21:19:39

Here is my query :

connection.query("SELECT last_disconnect FROM users WHERE uuid=?", [userId])

Here is what I got with the query :

2019-06-04T20:19:39.000Z

Here is what I got when I create a new date with this :

console.log(new Date(dateFromRequest))

->

Tue Jun 04 2019 22:19:39 GMT+0200 (heure d’été d’Europe centrale)

The datetime I want to get in Javascript is the datetime stored in Mysql :

2019-06-04 21:19:39

My time zone is GMT +2

When I check the datetime in Mysql it says Timezone +0200

Does someone know what is wrong with what I am doing ?

Thank you in advance !

Abdulsamet Kurt · Accepted Answer · 2019-06-04 20:36:28Z

4

mysqljs - Connection options#dateStrings

Execute query without dateStrings

connection.query('SELECT * FROM user', function (error, results, fields) {
    if (error) throw error;
    console.log(results[0].registerDate)
});

Output

_> node index.js
2018-06-04T20:13:17.000Z

Execute same query but with dateStrings

const connection = mysql.createConnection({
    user: 'root',
    password: '1234',
    database: 'test',
    dateStrings: true // yo, im here
});

Output

_> node index.js
2018-06-04 23:13:17

Simple example

connection.query('SELECT * FROM user', function (error, results, fields) {
    if (error) throw error;
    results.map(x => {
        const date = new Date(x.registerDate);
        console.log(date.getFullYear())
    })
});

Output

_> node index.js
2018

answered Jun 4, 2019 at 20:36

Abdulsamet Kurt

1,2512 gold badges12 silver badges16 bronze badges

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

1 Comment

Erwan Over a year ago

Thank you ! It's perfect :) I didn't think there were an option like this I should have searched for it

Collectives™ on Stack Overflow

Mysql datetime different in Javascript

1 Answer 1

Simple example

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Simple example

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related