Difference in data between Nodejs SQL Query and MySQL Query

Question

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".

One is probably setting a connection timezone, the other isn't, and MySQL is helpfully translating dates. — AKX
– AKX, Commented Apr 21, 2020 at 12:26
are you sure the exact query is run on both cases? Can you post the actual query strings used in both cases? — Nikos M.
– Nikos M., Commented Apr 21, 2020 at 12:29
another reason might be that the node queries a different db or table than the direct query — Nikos M.
– Nikos M., Commented Apr 21, 2020 at 12:30
@NikosM.added in the query strings and a small explanation with that. — Wiliam Cardoso
– Wiliam Cardoso, Commented Apr 21, 2020 at 12:40
@AKX I'm thinking you may be right as the times are slightly off in comparison, is there somewhere I can look at this for doing it correctly in node? — Wiliam Cardoso
– Wiliam Cardoso, Commented Apr 21, 2020 at 12:41

Wiliam Cardoso · Accepted Answer · 2020-04-21 13:18:34Z

1

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'

answered Apr 21, 2020 at 13:18

Wiliam Cardoso

4541 gold badge9 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

Difference in data between Nodejs SQL Query and MySQL Query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related