I need to calculate the number of "working minutes" between two datetime values, lets call them 'Created' and 'Finished'.

'Finished' is always subsequent to 'Created'. The two values can differ by anything from 1 second to several years. The median difference is 50,000 seconds or roughly 14 hours.

Working minutes are defined as those occurring between 0900 to 1700 hours, Monday to Friday; excluding weekends and official holidays in our country.

I decided a lookup table was the way to go, so I generated a table of all work minutes, explicitly excluding weekends, nights and holidays...

CREATE TABLE `work_minutes` (
  `min` datetime NOT NULL,
  PRIMARY KEY (`min`),
  UNIQUE KEY `min_UNIQUE` (`min`)
)

I populated this programatically with all the "working minutes" between years 2017 to 2024, and at this point I started to get the feeling I was being very inefficient as the table began to balloon to several hundred thousand rows.

I can do a lookup easily enough, for instance:

SELECT COUNT(min) FROM `work_minutes` AS wm
WHERE wm.min > '2022-01-04 00:04:03'
AND wm.min <= '2022-02-03 14:13:09';
#Returns 10394 'working minutes' in 0.078 sec

This is good enough for a one-off lookup but to query a table of 70,000 value pairs takes over 90 minutes.

So, I am uncomfortable with the slowness of the query and the sense that the lookup table is unnecessarily bloated.

I am thinking I need to set up two tables, one just for dates and another just for minutes, but not sure how to implement. Date logic has never been my forte. The most important thing to me is that the lookup can query over 70,000 values reasonably quickly and efficiently.

Working in MySQL 5.7.30. Thanks in advance for your expertise.

CodePudding user response：

1. Divide the timerange to 3 parts - starting and finishing incomplete day parts, and middle part which consists from a lot of complete days. Of course if both starting and finishing time stamps have the same date part then it will be one part only, if their dates are consecutive then you\ll have 2 parts to process.
2. There is no problem to calculate the number of working minutes in incomplete day part. Common overlapping formula with weekday checking will help.
3. Create static calendar/service table which starts from the date which is earlier than any possible date in your beginning timestamp with guarantee and includes all dates after any possible date in your finishing timestamp. Calculate cumulative working minutes for each date in the table. This table allows to calculate the amount of working time in any range of complete days with single substraction.

CodePudding user response：

Plan A: Convert the DATETIME values to seconds (from some arbitrary time) via TO_SECONDS(), then manipulate them with simple arithmetic.

Plan B: Use the DATEDIFF() function.

Your COUNT(min) counts the number of rows where min IS NOT NULL. You may as well say COUNT(*). But did you really want to count the number of rows?