CODEWITHSUNDEEP
mysqldatetime
Josh Fradley
Josh Fradley

Reputation: 562

Mysql not between time and dates

user | completed
mike | 2016-07-10 19:00:00
john | 2016-07-11 08:00:00

I am trying to select all rows in a database where the row completed is NOT between 14:00 the previous day and the current day before 10:00. The script is designed to be run at 10:30 everyday

I've tried this

SELECT name FROM daily_tracking WHERE completed NOT BETWEEN now() - interval 1 day AND NOW() - INTERVAL 8 hour

Upvotes: 0

Views: 403

Answers (3)

Strawberry
Strawberry

Reputation: 33945

You can also write it this way, which I prefer...

SELECT dt.name
  FROM daily_tracking dt
 WHERE dt.completed BETWEEN CURDATE() - INTERVAL 14 HOUR AND CURDATE() + INTERVAL 10 HOUR;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269793

I would not depend on the exact time when the script is being run. Instead, use arithmetic based on the current date:

SELECT dt.name
FROM daily_tracking dt
WHERE completed < date_sub(curdate(), interval (24 - 14) hour) or
      completed > date_add(curdate(), interval 10 hour);

This will work on a given day, regardless of the time the script is run.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

you should use date_sub 

SELECT name 
FROM daily_tracking 
WHERE completed NOT BETWEEN date_sub(NOW(), interval 1 day ) 
   AND date_sub(NOW(),  INTERVAL 8 hour)

Upvotes: 1

Related Questions