CODEWITHSUNDEEP
mysqlsqldatedatetime
Kutasek
Kutasek

Reputation: 11

MYSQL select date now-1 day with TIME and between date

I want to select all rows between current day-1 with specific time 08:00am and to date now also with specific time. This is not working for example sample line code

WHERE SOME_DATE_COLUMN BETWEEN DATE_SUB(DATE(NOW() - INTERVAL 1 DAY) '08:00:00', '%Y-%m-%d %H:%i:%s') AND DATE_SUB(DATE(NOW()) '08:00:00', '%Y-%m-%d %H:%i:%s');

I tried also DATE_FORMAT

WHERE SOME_DATE_COLUMN BETWEEN DATE_FORMAT(DATE(NOW() - INTERVAL 1 DAY) '08:00:00', '%Y-%m-%d %H:%i:%s') AND DATE_FORMAT(DATE(NOW()) '08:00:00', '%Y-%m-%d %H:%i:%s');

If I run sql today I want to select all record between current date minus 1 day from 08:00am to current date 08:00am

Upvotes: 0

Views: 3601

Answers (1)

GMB
GMB

Reputation: 222402

I would do:

where 
    some_date_column >= current_date - interval 16 hour
    and some_date_column < current_date + interval 8 hour

current_date gives you the current date (without the time part). You can then add and substract the required number of hours.

Note that this query does not use between, but instead half-open intervals. If you want consistent, adjacent segments in time, then you want to exclude one of the bounds (by convention, generally the outer bound is excluded).

Demo on DB Fiddle:

select current_date - interval 16 hour, current_date + interval 8 hour

current_date - interval 16 hour | current_date + interval 8 hour
:------------------------------ | :-----------------------------
2020-02-04 08:00:00             | 2020-02-05 08:00:00

Upvotes: 1

Related Questions