0

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.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Telescope2334
  • 177
  • 8

2 Answers2

0
  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.
Akina
  • 39,301
  • 5
  • 14
  • 25
0

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?

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thanks @Rick James -- yes, my lookup table just includes working minutes, and no nulls; so a count(*) does return the number I want. But it certainly seems slow and inefficient to do it this way... If we select * from the table you will see every working minute listed, e.g. 2022-05-20 09:01:00 2022-05-20 09:02:00 2022-05-20 09:03:00... etc – Telescope2334 May 25 '22 at 07:01
  • 1
    One person for one year = 120,000 rows in the table? Ugh. Think about recording start and finish, not each minute. – Rick James May 25 '22 at 20:43