I've got 2 datetime cols that represent start of work and end of work: field_dates_value field_dates_value2
I have to SUM day hours of work and night hours of work grouped by month.
The complex thing is that for my business night is starting à 20:00:00 and is ending à 05:59:00. If a man start is work at 18:00 and end à 22:00, the result is that hours before 20:00 are considered as day work and hours after 20:00 are considered as night work. So in in this case 2 hours of day's work and 2:00 of night's work.
In the example below i can SUM timestamps between 2 dates grouped by day and another col:
field_dates_value 2018-07-12 18:00:00 field_dates_value2 2018-07-13 02:00:00 SELECT SUM( UNIX_TIMESTAMP( field_dates_value2 ) - UNIX_TIMESTAMP( field_dates_value ) ) AS total, bundle, DAY( field_dates_value ) AS day FROM `bons_field_revision_field_dates` WHERE field_dates_value > '2018-07-11 00:00:00' GROUP BY bundle, day ORDER BY day, bundle LIMIT 0 , 30
My problem is that i don't know how to get sums form an interval of 2 cols before and after 20:00 .
Any ideas how to achieve this?
thank you very muche for your help