Group by time that spans 2 days-CodePudding

I am trying to get the month sales for a business where the time they are open is from 11am-2am the next day. I tried doing the following by grouping each day assuming that just for worse case they do sales until 3am. Assuming this is 16 hours of sales. I haven't checked if the total is correct yet but I do notice that it only gives me back 29 days where there are 31 days in January. a_invoices is the master invoice for each and a_tabs is the details for each invoice.

Am I doing this correctly? I know basic MySql and this is a bit out of my pay grade. Thanks for any help.

SELECT DATE(a_invoices.startdate - INTERVAL 16 HOUR) as day , sum(a_tabs.Total)
FROM a_invoices
Right JOIN a_tabs on a_invoices.TabId = a_tabs.TabId
WHERE a_invoices.closedate BETWEEN '2022-01-01 11:00:00' and '2022-01-30 03:00:00'
AND a_invoices.status='c' and a_tabs.status<>'v'
GROUP BY day

CodePudding user response：

You have written from the 1st to the 30th. Try from the 1st to the 1st. It will also be easier to update the query each month

SELECT DATE(a_invoices.startdate - INTERVAL 16 HOUR) as day , sum(a_tabs.Total)
FROM a_invoices
Right JOIN a_tabs on a_invoices.TabId = a_tabs.TabId
WHERE a_invoices.closedate BETWEEN '2022-01-01 11:00:00' and '2022-02-01 03:00:00'
AND a_invoices.status='c' and a_tabs.status<>'v'
GROUP BY day