2

I have a column updatedAt in my table User which is of Date type, it stores date along with time. I want to query using only date and not datetime. I am using sequelize Sequelize.Op.gt and Sequelize.Op.lt operators to get Users updated on that exact date regardless of time by adding 24 * 60 * 60 * 1000. But the date is not incrementing one day. When I try to subtract, it is working flawlessly. I could add one day only after using getTime() method.

I'm confused as to why it works when subtracting without using getTime() but doesn't work when adding. Could anyone explain?

TL;DR

This works:

[Sequelize.Op.gt]: new Date(new Date(updatedAt) - 24 * 60 * 60 * 1000)
//updateAt: 2018-11-27
//output: 2018-11-26

This doesn't work:

[Sequelize.Op.gt]: new Date(new Date(updatedAt) + 24 * 60 * 60 * 1000)
//updateAt: 2018-11-27
//output: 2018-11-27

And this works:

[Sequelize.Op.lt]: new Date(new Date(updatedAt).getTime() + 24 * 60 * 60 * 1000)
//updateAt: 2018-11-27
//output: 2018-11-28
niaS
  • 323
  • 4
  • 18

2 Answers2

5
sequelize.fn("DATEADD", sequelize.literal("DAY"), 4, sequelize.col('Your Date')) // 4 number of days adding

If want to find the difference including this added date from current date

sequelize.fn('DATEDIFF', sequelize.literal("DAY"), sequelize.fn("DATEADD", sequelize.literal("DAY"), 4, sequelize.col('Your Date')), sequelize.fn("GETDATE")
ABDUL JAMAL
  • 452
  • 7
  • 12
  • You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATEADD(DAY, 1, `schedule_in`), `created_at` DATETIME NOT NULL, `updated_at` DAT' at line 1 – Yogi Arif Widodo Aug 06 '22 at 10:21
2

Let's see your 2nd case step by step
1. new Date(new Date(updatedAt) + 24 * 60 * 60 * 1000)
2. new Date(new Date(updatedAt) + 86400000)
3. new Date('Tue Nov 27 2018 17:34:48 GMT+0800 (Some Region)86400000')
and Date parser ignored '86400000' part of string from input.
4. new Date('Tue Nov 27 2018 17:34:48 GMT+0800 (Some Region)')
Now your input is not modified. Javascript doesn't know are you doing string add or number add. This is your confusing point. On other case integer addition and subtraction operators done properly

enxtur
  • 2,395
  • 1
  • 16
  • 16