How to SELECT a date between two dates

Question

How can I select from mysql with php a range between 2 dates? The working day, start for example, at 7:00 am of 2022-01-01 and ends at 2:00 am of 2022-01-02. I want that all collected datas from 00:00 to 2:00 am of 2022-01-02 are grouped inside previous date.

Now I use this query

 SELECT date_format(dateAdded,"%m-%d") as mth,COUNT(productID) as total, productID FROM statisticsNW WHERE userID = "35" AND productID = "1193'" AND YEAR(dateAdded) = "2022" AND month(dateAdded) = "01" GROUP by year(dateAdded),month(dateAdded), day(dateAdded) ORDER by year(dateAdded),month(dateAdded)

and the result is this but the "total" is wrong because is from 00:00 to 23:59 of the same day. The correct query the I want is from 7:00 to 6:59 of the next day. But from 00:00 to 6:59 datas must be counted in the previous day.

Answer 1

you can add 5 hours to the start_time and end_time, so the it appears the shift starts at midnight

select DATEDIFF(HH,cast('2022-01-24 19:00:00' as datetime),cast('2022-01-25 00:00:00' as datetime)) as time_5_hrs

select dateadd(HOUR,5,cast('2022-01-02 19:00:00' as datetime)) as starttime, dateadd(HOUR,5,cast('2022-01-03 02:00:00' as datetime)) as endtime,datediff(HH,dateadd(HOUR,5,cast('2022-01-02 19:00:00' as datetime)),dateadd(HOUR,5,cast('2022-01-03 02:00:00' as datetime))) diff

By doing this you can get both date are the same and you will be able to group it as single day.

Answer 2

I have solved

SELECT DATE_SUB(DATE_ADD("2017-06-15 5:30:00", INTERVAL 24 HOUR), INTERVAL 1 MINUTE);