Odd results with state aggregation / duration_in across time bucket boundaries

TimescaleDB and PostgreSQL
1

I was trying to get some help in another thread but now I don’t think interpolated_duration_in is really the fix for my problem so I’m starting a new thread.

I am pushing machine state changes (currently only Running, Stopped, and Offline) from Tulip to TimescaleDB. I’m then putting into 12 hour time buckets aligned to shift start times.

Right now I have two problems:

  1. The durations for complete shifts does not add up to 12 hours (sometimes not even close).

    My current assumption is that perhaps the state changes are not being carried over time_bucket intervals and I don’t see a way to force LOCF.

  2. I’m trying to use the running time / total time to calculate utilization but the math isn’t right most of the time. I added extraction of the durations into seconds and then cast them to float which changed the values but they’re still wrong.

Here’s my current sql statement:

--- 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;

I really need to get this working properly.

2

Here’s an example of a bad utilization calculation:

Shift Start Time Running Stopped Offline Total Utilization
2023-09-22 05:00:00.000 -0500 04:35:52 01:23:01 00:00:00 05:58:53 0.9811320754716981

Calculating it in Excel the Utilization should be 0.76868063

3

I updated the trigger in Tulip to not only write to TimescaleDB on event, but also once a minute. It’s technically redundant data but now when I look at the previous shift the total duration looks very close to 12 hours, which appears to confirm my assertion that states are not carried over to subsequent time buckets.

4

Hi @richard.s , it seems a issue that we’ll need to rework in the component. If you can open an issue on the toolkit project, probably more people can upvote it as it seems an actual problem for the end user to track it.