CODEWITHSUNDEEP
javascriptmysqlsqlnode.jsdatabase
Lion789
Lion789

Reputation: 4482

Mysql query time issue?

Hi so I am trying to grab all records that fall between 10 minutes ago and now using this query. 

SELECT task_id, task_title, task_assigned_phone_number, task_assigned_name, task_assigned_user_id, task_guest_id, task_creator_id
FROM TASKS
WHERE task_reminder IS NOT NULL AND
      task_reminder > ? AND
      task_reminder <= ? AND
      reminded = 0 AND
      task_complete = 0
ORDER BY task_reminder ASC";

I have the schema and SQL query up on sqlfiddle and as you run the query you can see 0 records fall in place. The times I included where based on new Data().getTime() on nodejs. What should I be doing? I tried new Date()..toUTCString() but still having issues.

http://sqlfiddle.com/#!9/c6a361/7

Upvotes: 0

Views: 55

Answers (2)

kranky
kranky

Reputation: 1

It's an issue with your timestamp format. Try this: 

SELECT * FROM TASKS 
WHERE task_reminder IS NOT NULL 
AND task_reminder > '2019-09-17 00:00:00' 
AND task_reminder <= '2019-09-18 00:00:00' 
AND reminded = 0 
AND task_complete = 0 
ORDER BY task_reminder ASC;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I'm not sure why you are using Unix epochs for a timestamp column. I would expect a WHERE clause like this:

WHERE t.task_reminder IS NOT NULL AND
      t.task_reminder > now() - interval 10 minute AND
      t.reminded = 0 AND
      t.task_complete = 0

You might also need t.task_reminder < now(), if you have future reminders.

Here is a sql<>fiddle. Note that the data in the table needs to change to demonstrate this.

Upvotes: 1

Related Questions