0

I am attempting to create a web (react/sequelize) front end to an existing database.

When I perform a select from the database the returns results in sequelize have the date as off by 1 (-1).

Here is the SQL and code used to get the data:

let sql1 = 'SELECT count(RECEIVED_DATE) as CallCount, CAST(RECEIVED_DATE AS DATE) as CallDate FROM TowCalls WHERE OFFICE_ID = ? AND (RECEIVED_DATE BETWEEN dateadd(day,?, GETDATE()) AND GETDATE()) group by CAST(RECEIVED_DATE AS DATE) ORDER BY CAST(RECEIVED_DATE AS DATE)';
    models.sequelize.query(sql1,  { replacements: [id, -7], type: models.sequelize.QueryTypes.SELECT })
      .then(function(summary) {

When I output summary to the console I get:

[
  { CallCount: 22, CallDate: '2020-12-16' },
  { CallCount: 36, CallDate: '2020-12-17' },
  { CallCount: 20, CallDate: '2020-12-18' },
  { CallCount: 12, CallDate: '2020-12-19' },
  { CallCount: 28, CallDate: '2020-12-20' },
  { CallCount: 13, CallDate: '2020-12-21' },
  { CallCount: 1, CallDate: '2020-12-22' }
]

When I use the same query in SQL Server itself I get the same counts, but all the dates are +1.

Although I never specifically set it, I believe the dates are in SQL in EST timezone.

All the users of the database are in the same timezone so I never had to worry about tracking any changes.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Johnne
  • 143
  • 10
  • Are you saying that your dates are off by one day? What is the data type of the `RECEIVED_DATE` column? – Alex Dec 24 '20 at 12:47
  • Yes, the last date in the list should be 2020-12-23, not the 22nd. RECEIVED_DATE is a datetime column in MSSQL. The value in that row in SQL Server is: 2020-12-23 01:30:00.000 I suspect it has something to do with UTC time and the conversion of the EST time to UTC. But I am not sure how to deal with it. – Johnne Dec 24 '20 at 16:18
  • Should I update all the dates in the database to be stored in UTC? In my case I am assuming that would mean adding +5 hours to all the datetime columns. – Johnne Dec 24 '20 at 16:20
  • I added 5 hours to the RECEIVED date and the returned dataset in sequelize still only contains 2020-12-22, where it should be 23rd. The values of the count have changed, so I am not sure adding 5 hours is the way to go. – Johnne Dec 24 '20 at 16:29
  • Could the issue have something to do with CAST I am performing on the date? I changed to query to return the TOP(10) and RECEIVED_DATE (removing the COUNT, CAST and GROUP BY) and the values are correct. – Johnne Dec 24 '20 at 16:35
  • Additional, testing the above test did return the correct dates, although they appear ar UTC dates (2020-12-23.000.000.000Z). When I use them in a date object they are converted and changed to 2020-12-22. – Johnne Dec 24 '20 at 18:38

0 Answers0