Division of extracted duration_in doesn't work as expected.

[hobbes1069]

Relevant system information:

• OS: RHEL 8.6
• PostgreSQL version (output of SELECT version();): PostgreSQL 15.2 (Ubuntu 15.2-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit
• TimescaleDB Toolkit version (output of \dx timescaledb_toolkit in psql): 1.16.0
• Installation method: docker (podman)

Describe the bug
I'm attempting to aggregate state data for several CNC machines which can easily have a cycle time of 2-4.5 hours. I'm attempting to calculate %utilization by dividing the running time by the running+stopped time. I am extracting the duration to seconds and casting to a float but the results are still wrong in most cases.

Here's the query:

--- OEE Utilization reporting by shift, cell, asset
SELECT shift_start_time, shift_abcd as shift_abcd, cell, asset, duration_in(agg, 'Running') as running, duration_in(agg, 'Stopped') as stopped,
  duration_in(agg, 'Offline') as offline, duration_in(agg, 'Running') + duration_in(agg, 'Stopped') + duration_in(agg, 'Offline') as total,
CASE
    WHEN extract(SECONDS from duration_in(agg, 'Running')) + extract(SECONDS from duration_in(agg, 'Stopped')) = 0
    THEN 0
    ELSE cast(extract(SECONDS from duration_in(agg, 'Running')) as float) / cast(extract(SECONDS from duration_in(agg, 'Running')) + extract(SECONDS from duration_in(agg, 'Stopped')) as float)
END as utilization
from (SELECT time_bucket('12 hours', timestamp - interval '5 hours', 'America/Chicago') + interval '5 hours' as shift_start_time,
  shift_abcd.shift as shift_abcd, cell, asset, state_agg(timestamp, state) as agg
FROM machine_state 
left join shift_abcd on time_bucket('12 hours', timestamp - interval '5 hours', 'America/Chicago') + interval '5 hours' = public.shift_abcd.shift_start
GROUP BY shift_start_time, shift_abcd, cell, asset
ORDER BY shift_start_time desc)sub;

Expected behavior
%utilization is calculated correctly.

Actual behavior
% Utilization is calculated incorrectly in almost all cases:

Timestamp	Shift	Cell	Asset	Running	Stopped	Offline	Total	% Utilization
2023-11-01 05:00:00.000 -0500	B	MV3	2759	01:00:59	00:40:31	00:00:00	01:41:30	0.6555555555555556
2023-11-01 05:00:00.000 -0500	B	MH5	3618	02:25:35	00:53:22	00:00:00	03:18:57	0.6140350877192983
2023-11-01 05:00:00.000 -0500	B	MV2	2635	00:02:38	01:52:31	00:00:00	01:55:09	0.5507246376811594
2023-11-01 05:00:00.000 -0500	B	MV3	2760	00:00:00	02:41:42	00:00:00	02:41:42	0.0
2023-11-01 05:00:00.000 -0500	B	MH4	3154	02:08:10	00:37:09	00:00:00	02:45:19	0.5263157894736842
2023-11-01 05:00:00.000 -0500	B	MV3	2811	01:38:41	00:47:27	00:00:00	02:26:08	0.6029411764705882
2023-11-01 05:00:00.000 -0500	B	MV1	2955	01:51:12	01:10:34	00:00:00	03:01:46	0.2608695652173913
2023-11-01 05:00:00.000 -0500	B	MH1	3396	01:31:35	01:25:56	00:00:00	02:57:31	0.38461538461538464
2023-11-01 05:00:00.000 -0500	B	MH1	3320	00:00:00	02:54:18	00:00:00	02:54:18	0.0
2023-11-01 05:00:00.000 -0500	B	MH5	3619	02:37:21	00:42:00	00:00:00	03:19:21	1.0
2023-11-01 05:00:00.000 -0500	B	MV4	2630	01:07:19	01:16:06	00:00:00	02:23:25	0.76
2023-11-01 05:00:00.000 -0500	B	MH2	3155	00:38:23	01:26:07	00:00:00	02:04:30	0.7666666666666667
2023-11-01 05:00:00.000 -0500	B	MH1	3395	00:31:29	02:23:52	00:00:00	02:55:21	0.35802469135802467
2023-11-01 05:00:00.000 -0500	B	MV4	2791	01:41:51	00:51:31	00:00:00	02:33:22	0.6219512195121951
2023-11-01 05:00:00.000 -0500	B	MH4	3153	01:30:50	00:25:49	00:00:00	01:56:39	0.5050505050505051
2023-11-01 05:00:00.000 -0500	B	MV1	2956	00:30:39	00:21:27	00:00:00	00:52:06	0.5909090909090909
2023-11-01 05:00:00.000 -0500	B	MH2	3156	00:39:59	01:25:20	00:00:00	02:05:19	0.7468354430379747
2023-11-01 05:00:00.000 -0500	B	MH3	3354	01:15:28	02:03:12	00:00:00	03:18:40	0.7
2023-11-01 05:00:00.000 -0500	B	MH4	3216	02:24:51	00:12:23	00:00:00	02:37:14	0.6891891891891891
2023-11-01 05:00:00.000 -0500	B	MH3	3318	01:23:34	01:54:49	00:00:00	03:18:23	0.40963855421686746
2023-11-01 05:00:00.000 -0500	B	MV1	2912	01:59:55	01:03:19	00:00:00	03:03:14	0.7432432432432432
2023-11-01 05:00:00.000 -0500	B	MV2	2792	01:58:39	00:42:10	00:00:00	02:40:49	0.7959183673469388
2023-11-01 05:00:00.000 -0500	B	MV2	2911	00:38:56	01:12:34	00:00:00	01:51:30	0.6222222222222222
2023-11-01 05:00:00.000 -0500	B	MH3	3355	01:21:05	01:58:36	00:00:00	03:19:41	0.12195121951219512
2023-10-31 17:00:00.000 -0500	C	MH2	3155	07:45:57	04:12:39	00:00:00	11:58:36	0.59375
2023-10-31 17:00:00.000 -0500	C	MH5	3619	10:00:15	01:37:02	00:00:00	11:37:17	0.8823529411764706
2023-10-31 17:00:00.000 -0500	C	MH4	3154	00:00:07	00:44:57	00:00:00	00:45:04	0.109375
2023-10-31 17:00:00.000 -0500	C	MH3	3318	08:46:41	03:00:54	00:00:00	11:47:35	0.43157894736842106
2023-10-31 17:00:00.000 -0500	C	MH4	3216	08:14:49	03:26:48	00:00:00	11:41:37	0.5051546391752577

Additional context
I can provide raw data if needed but I may need to sanitize it.