2

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

Wiliam Cardoso
  • 434
  • 1
  • 8
  • 23
  • One is probably setting a connection timezone, the other isn't, and MySQL is helpfully translating dates. – AKX Apr 21 '20 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. Apr 21 '20 at 12:29
  • another reason might be that the node queries a different db or table than the direct query – Nikos M. Apr 21 '20 at 12:30
  • @NikosM.added in the query strings and a small explanation with that. – Wiliam Cardoso Apr 21 '20 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 Apr 21 '20 at 12:41
  • you can use this in your query ```DATE_FORMAT(YOUR_DATE, "%Y/%m/%d %H:%i:%s")```. So query will be like ```SELECT date FROM Availability WHERE NurseNo = ${data.NurseNo} AND (date BETWEEN DATE_FORMAT(${data.DateFrom}, "%Y/%m/%d 01:00:00") AND DATE_FORMAT(${data.DateTo}, "%Y/%m/%d")) ORDER BY Date ASC;``` – Arjun Singh Apr 21 '20 at 13:02
  • @ArjunSingh tried that but still pulls the same data as in the original post on the node server :/ – Wiliam Cardoso Apr 21 '20 at 13:12
  • sorry my bad, you need to do this ==> ```SELECT DATE_FORMAT(date, "%Y-%m-%d %H:%i:%s") AS date FROM Availability WHERE NurseNo = ${data.NurseNo} AND (date BETWEEN DATE_FORMAT(${data.DateFrom}, "%Y-%m-%d 01:00:00") AND DATE_FORMAT(${data.DateTo}, "%Y-%m-%d")) ORDER BY Date ASC;``` – Arjun Singh Apr 21 '20 at 14:22

1 Answers1

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'
Wiliam Cardoso
  • 434
  • 1
  • 8
  • 23