MYSQL query that works out the total amount of days between different time periods for a payroll

Question

I know this question may seem like a simple one and might have been asked before but what makes this tricky is how do I work out the total days for a payroll. Now my company's drivers work on how many days they are clocked onto a truck. I need to calculate the total amount of days they have been clocked in per month.

The payroll dates are the 25th of the previous month and the 26th of the current month. If a driver is clocked in before the 26th of the payroll month the amount of days still need to be calculated up until the 26th. Similarly if the driver was clocked in before the 25th and only clocks out during the payroll month only the days between the 25th and the clocked out date needs to be calculated.

I have a php solution to this but it created so many over heads and is not optimized at all, it also creates loading problems and the way the calculations are done is based of the amount clock outs and manually adding or subtracting days according to certain conditions. This is not a a very good way of calculating the amount of clocked in days per driver as you can see so I am trying to make a query that will handle the calculation of the days for the payroll.

the problem I have with this current query is that it seems not able to see that the date 2022-04-06 20:42 is smaller than 2022-04-25 07:59

provided below is the query and the data it's returning.... Just excuse the query it does seem a bit hard to follow as I still need learn how to create MySQL variables so there's a lot of repetition. (The STR_TO_DATE() is becasue the dates are being saved as type strings in the data base)

MySQL query:

SELECT 
IF(STR_TO_DATE(OPENING_DATE, '%Y-%m-%d %H:%i') < DATE_FORMAT(CURRENT_DATE() - INTERVAL 1 MONTH, '%Y-%m-26 00:01'), 
DATE_FORMAT(CURRENT_DATE() - INTERVAL 1 MONTH, '%Y-%m-26 00:01'),OPENING_DATE) AS OPENING_DATE,

IF(STR_TO_DATE(CLOSING_DATE, '%Y-%m-%d %H:%i') = '', 
DATE_FORMAT(CURRENT_DATE(), '%Y-%m-25 23:59'), CLOSING_DATE) AS CLOSING_DATE,

TIME_TO_SEC(TIMESTAMPDIFF( MINUTE,

IF(STR_TO_DATE(OPENING_DATE, '%Y-%m-%d %H:%i') = '' , 
DATE_FORMAT(CURRENT_DATE() - INTERVAL 1 MONTH, '%Y-%m-26 00:01'),OPENING_DATE),

IF(STR_TO_DATE(CLOSING_DATE, '%Y-%m-%d %H:%i') < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-25 23:59'), 
DATE_FORMAT(CURRENT_DATE(), '%Y-%m-25 23:59'), CLOSING_DATE)
))/3600 AS TIME_DIFF_01

FROM HANDOVERS
WHERE (DRIVER_ID = '8308215990085');

Returned Data:

For reference this is the current data saved in the data base. Here you can compare the saved clocked in and out data to the data I'm trying to pull. I honestly don't know why the second batch of data has different days to the first.

Answer 1

How about this...

• When you insert a row, check to see if it spans the month boundary (morning of the 26th). If it does, split it into two (or more) records.

• Add an extra column that says which month the row belongs to.

With those, the Select becomes much simpler.