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.
Relevant system information:
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\dx timescaledb_toolkitinpsql): 1.16.0Describe 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
runningtime by therunning+stoppedtime. 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:
Expected behavior %utilization is calculated correctly.
Actual behavior % Utilization is calculated incorrectly in almost all cases:
Additional context I can provide raw data if needed but I may need to sanitize it.