Mysql query time issue?

Question

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

Gordon Linoff · Accepted Answer · 2019-09-18 00:40:44Z

1

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.

edited Sep 18, 2019 at 0:40

answered Sep 17, 2019 at 23:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Lion789 Over a year ago

Can you update it in the sqlfiddle and send me the new link -sqlfiddle.com/#!9/c6a361/9 I tried it like so and doesnt seem to work

Ibu Over a year ago

copy and paste line one and two to your where clause.

Lion789 Over a year ago

It is in my where clause... or am I missing something?

kranky · Accepted Answer · 2019-09-18 01:09:03Z

0

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;

answered Sep 18, 2019 at 1:09

kranky

11 bronze badge

Collectives™ on Stack Overflow

Mysql query time issue?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related