Mysql Date different while retrieving from node.js

Question

I want to query the records while their insertion date is between two dates and that is like this:

SELECT `products`.`Product_Name`, `products`.`Product_Type`, `products`.`Product_Date` Where 
`products`.`Product_Date` between CONCAT(YEAR(CURRENT_DATE),"- 
 ",MONTH(CURRENT_DATE),"-","01") and (LAST_DAY(CURRENT_DATE))

the result in MySql Is like this:

but in the server-side (I use Node.js) the result is different and that is like this

[
   {"Product_Name":"Water","Product_Type":"Large","Product_Date":"2018-03-24T18:30:00.000Z"},
    {"Product_Name":"Water","Product_Type":"Small","Product_Date":"2018-03-24T18:30:00.000Z"},
    {"Product_Name":"Water","Product_Type":"Large","Product_Date":"2018-02-28T18:30:00.000Z"},
    {"Product_Name":"Water","Product_Type":"Large","Product_Date":"2018-03-30T18:30:00.000Z"}
]

I want to query the inserted products in the current month from 1st till last date of current month, I get the right result in mysql but wrong result in the server node js, in the third object it shows also the previous month date too. not only that, even the format of the date is also different in the server side

Mike Atkins · Accepted Answer · 2018-03-26 00:59:15Z

4

This looks like a timezone issue. The node dates are using a standard format (ISO 8601) for representing date/time. The Z at the end indicates the time is in UTC time. Mysql is showing you the time in local time. See https://dev.mysql.com/doc/refman/5.7/en/datetime.html. Local time can mean either mysql server timezone or timezone for your connection (might be the same), depending on how you setup your connection. My guess, from the above output, is that you are in a timezone that is at least 5 and half hours ahead of UTC time. UTC+5:30 would be India or Sri Lanka. So maybe you are in one of those places, or to the east of there. Some options, in your node mysql connection you can set the timezone. E.g., see the connection options here: https://www.npmjs.com/package/mysql#establishing-connections

edited Mar 26, 2018 at 0:59

answered Mar 25, 2018 at 18:08

Mike Atkins

1,59113 silver badges11 bronze badges

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

Comments

Lakshya Sharma · Accepted Answer · 2019-12-13 20:47:05Z

4

I faced the same issue and co-incidentally, the same time difference (I'm also from GMT+05:30).

All you have to do, is while establishing connection to the database, set the timezone. Like this:

const mysql = require('mysql');
var connection = mysql.createConnection({
    host     : <Your Host>,
    port     : <Your MySQL Port>,
    user     : <Username>,
    password : <Password>,
    database : <Database>,
    timezone : "+00:00"
});

This will give you the date or datetime sql value as a javascript Date() object with same value, for you to change according to the timezone of your need... :)

answered Dec 13, 2019 at 20:47

Lakshya Sharma

677 bronze badges

Comments

Grant · Accepted Answer · 2022-12-15 07:50:22Z

3

Adding -> dateStrings: true, -> to your MySQL config will give you the date in the same format as your database: 2018-02-01

const mysql = require('mysql');
var connection = mysql.createConnection({
    host: 'host',
    user: 'username',
    password: 'password',
    database: 'database',
    dateStrings: true,
});

Output: "Product_Date":"2022-12-15"

answered Dec 15, 2022 at 7:50

Grant

2,4412 gold badges31 silver badges41 bronze badges

Collectives™ on Stack Overflow

Mysql Date different while retrieving from node.js

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related