javascript - Mysql Date different while retrieving from node.js - Stack Overflow

admin管理员组

文章数量:1291345

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

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

• javascript
• mysql
• node.js

Share Improve this question Follow edited Jun 20, 2020 at 9:12 CommunityBot 111 silver badge asked Mar 25, 2018 at 11:25 m.om.o 11311 gold badge22 silver badges99 bronze badges

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 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./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./package/mysql#establishing-connections

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... :)

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"

本文标签： javascriptMysql Date different while retrieving from nodejsStack Overflow