A way to roughly calculate how long tasks took to complete

task_calculation.sql

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;