Skip to content

Instantly share code, notes, and snippets.

@kwburnett
Last active July 30, 2023 16:40
Show Gist options
  • Save kwburnett/f5297ee7883460543c2f479f2d6bdff7 to your computer and use it in GitHub Desktop.
Save kwburnett/f5297ee7883460543c2f479f2d6bdff7 to your computer and use it in GitHub Desktop.
A way to roughly calculate how long tasks took to complete
WITH holidays AS (
SELECT
'2023-12-25'::date AS holiday_date
UNION
SELECT
'2022-12-26'::date
UNION
SELECT
'2023-11-23'::date
),
work_hours AS (
-- Work day is 9 hours - adjust as needed, including for lunch
SELECT '08:00'::time AS start_time, '17:00'::time AS end_time
),
tasks AS (
SELECT
1 AS task_id,
'2022-12-10 13:46:54'::timestamp AS start_datetime,
'2023-01-01 14:22:16'::timestamp AS end_datetime
UNION
SELECT
2,
'2023-07-21 00:00'::timestamp,
'2023-07-21 12:00'::timestamp
),
adjusted_tasks AS (
SELECT
task_id,
-- If the start/end time is outside working hours for the day, just set it to the start/end of work hours
CASE
WHEN t.start_datetime::time > wh.end_time THEN (t.start_datetime::date + wh.end_time)::timestamp
WHEN t.start_datetime::time < wh.start_time THEN (t.start_datetime::date + wh.start_time)::timestamp
ELSE t.start_datetime END AS start_time,
CASE
WHEN t.end_datetime::time > wh.end_time THEN (t.end_datetime::date + wh.end_time)::timestamp
WHEN t.end_datetime::time < wh.start_time THEN (t.end_datetime::date + wh.start_time)::timestamp
ELSE t.end_datetime END AS end_time
FROM
tasks t
CROSS JOIN work_hours wh
),
task_dates AS (
SELECT *
FROM
(
-- Generate all dates between the min and max start/end times for the tasks being considered
SELECT
GENERATE_SERIES((
SELECT
MIN(start_datetime)
FROM
tasks
)::date, (
SELECT
MAX(end_datetime)
FROM
tasks
)::date, '1d') AS day
) d
WHERE
EXTRACT(ISODOW FROM day) NOT IN (6, 7) -- Remove weekends from the dates
-- Remove holidays
AND day NOT IN (
SELECT
holiday_date
FROM
holidays
)
),
task_times AS (
SELECT
at.task_id,
-- Calculate the minutes in a day spent on a task
CASE
-- If there isn't a match (i.e. work done on a weekend or it's a holiday), no time spent
WHEN td.day IS NULL THEN 0
-- If the task started and ended on the same day, just handle times
WHEN at.start_time::date = td.day AND at.end_time::date = td.day
THEN EXTRACT(EPOCH FROM (at.end_time::time - at.start_time::time)) / 60
-- If a task started on a particular day, subtract the start time from the end of the work day
WHEN at.start_time::date = td.day THEN EXTRACT(EPOCH FROM (wh.end_time - at.start_time::time)) / 60
-- If a task ended on a particular day, subtract the start of the work day from the task end time
WHEN at.end_time::date = td.day THEN EXTRACT(EPOCH FROM (at.end_time::time - wh.end_time)) / 60
-- Otherwise it's a full day's work
ELSE EXTRACT(EPOCH FROM (wh.end_time - wh.start_time)) / 60 END AS day_time
FROM
adjusted_tasks at
LEFT JOIN task_dates td
ON td.day BETWEEN at.start_time::date AND at.end_time::date
CROSS JOIN work_hours wh
)
SELECT
t.task_id,
-- Add all times for all days for all a task up to get the total time
SUM(tt.day_time) AS total_time
FROM
tasks t
JOIN task_times tt
ON tt.task_id = t.task_id
GROUP BY
t.task_id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment