CODEWITHSUNDEEP
mysqlnode.js
Wiliam Cardoso
Wiliam Cardoso

Reputation: 444

Difference in data between Nodejs SQL Query and MySQL Query

I have a Node server setup which is querying a mysql database, however the data returning from the node server is different to the data returning from running the queries directly in the mysql workbench.

I run the following query in Node:

SELECT date
FROM Availability 
WHERE NurseNo = 7320 AND 
(date BETWEEN '2020-08-01 01:00:00' AND '2020-09-01')
ORDER BY Date ASC;

This query returns the following records:

[
    {
        "date": "2020-08-01T23:00:00.000Z"
    },
    {
        "date": "2020-08-02T23:00:00.000Z"
    },
    {
        "date": "2020-08-03T23:00:00.000Z"
    },
    {
        "date": "2020-08-04T23:00:00.000Z"
    },
    {
        "date": "2020-08-05T23:00:00.000Z"
    },
    {
        "date": "2020-08-06T23:00:00.000Z"
    },
    {
        "date": "2020-08-07T23:00:00.000Z"
    },
    {
        "date": "2020-08-08T23:00:00.000Z"
    }
]

However when I run the exact same query in the mysql workbench I get these records back:

[
    {
        "date": "2020-08-01 00:00:00"
    },
    {
        "date": "2020-08-02 00:00:00"
    },
    {
        "date": "2020-08-03 00:00:00"
    },
    {
        "date": "2020-08-04 00:00:00"
    },
    {
        "date": "2020-08-05 00:00:00"
    },
    {
        "date": "2020-08-06 00:00:00"
    },
    {
        "date": "2020-08-07 00:00:00"
    },
    {
        "date": "2020-08-08 00:00:00"
    },
    {
        "date": "2020-08-09 00:00:00"
    }
]

How come the node server is incorrectly pulling back less records than the mysql workbench?

Edit for actual query strings:

The variables in the node string come from the url queries which are as follows:

availability/get-availability?NurseNo=7320&DateFrom=2020-08-01&DateTo=2020-09-01&loadDay=false

Node:

SELECT date
FROM Availability 
WHERE NurseNo = ${data.NurseNo} AND 
(date BETWEEN '${data.DateFrom} 01:00:00' AND '${data.DateTo}')
ORDER BY Date ASC;

MySQL Workbench:

SELECT *
FROM Availability
WHERE NurseNo = 7320 AND
(Date BETWEEN "2020/08/01 00:00:00" AND "2020/09/01")
ORDER BY Date ASC;

Having a look the times in both queries are slightly different, in the node query the date has the time "01:00:00" whereas the mySql has 00:00:00, I tried changing the mySql time to be the same as the node but that then doesn't show the date of "2020-08-01".

Upvotes: 2

Views: 566

Answers (1)

Wiliam Cardoso
Wiliam Cardoso

Reputation: 444

Found an answer which worked for me on another post: date format in node.JS

Adding this line into the connection config fixed it:

dateStrings: 'date'

Upvotes: 1

Related Questions