MYSQL query a weeks worth of data each day during a certain time range

All we need is an easy explanation of the problem, so here it is.

Currently the data i grab via my query is between a date AND time range however i want to change it so it querys a date range for a time range each day and output each days results(e.g 1/1/22 to 8/1/22 FROM 16:00-18:00 each day)

MY current query is bellow if you can recommend any changes.

AND created > ‘2021-09-09 13:00:00’ AND created <‘2021-09-09 16:00:00’

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This can be achieved by using MySQL DATE() and HOUR() functions.

Use:

select col1,col2...... 
from table_name
where  date(created ) between '2022-01-01' and '2022-01-08' 
and hour(created ) between '16' and '18' ;

Testing fiddle with different dates used

Important, DATE() and HOUR() functions don’t use an index if there is one on created column.

For larger data you might face serious performance issues.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply