CODEWITHSUNDEEP
mysql
Cris
Cris

Reputation: 13

MYSQL: How can I find records with a date field in a time range of 24 hours always starting from 6am to 6am next day

How can I find records with a date field in a time range of 24 hours with time always starting 6am to 5:59am next day.

Something like:

select * from events 
where timestamp >= UNIX_TIMESTAMP( 06:00:00 AM)
and timestamp < UNIX_TIMESTAMP(05:59:59 AM)

Is it possible?

I have 3 events starting from: 6am to 2pm, 2pm to 10pm and 10pm to 6am

I need to run a report within a 24 hrs time range with the clock starting everyday at 6am until next day 6am. I like the data to reset at 6am for the next cycle

Thanks

Upvotes: 1

Views: 647

Answers (2)

Razor Tag
Razor Tag

Reputation: 36

select * 
  from events 
 where timestamp between curdate() + interval 6 hour and curdate() + interval 1 day + interval 6 hour;

This should get events from 6AM current date to events for next day 6AM.

Upvotes: 1

LoneGunMan
LoneGunMan

Reputation: 1

select *
from events where
`timestamp` between date_sub(concat(curdate()," 06:00:00"), INTERVAL 1 DAY) and concat(curdate()," 05:59:59")

This query will get all events from 6AM yesterday to 5:59 today.

Upvotes: 0

Related Questions