0

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'

1
  • MySQL's TIME() function extracts just the time of day Jul 5, 2022 at 14:31

1 Answer 1

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.

1
  • Thank you this seems to be exactly what i needed, would it be possible add all of the figures from the individual days together so say i select a week date range i am met with 7 totals. Jul 6, 2022 at 11:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.