Asking here since we don't have a dedicated database channel and I'm trying to calculate % utilization (in the end). I have the following SQL query I'm performing in TimescaleDB. I'm polling CNC machines at 1Hz and using a trigger + stored function to calculate machine state. Now I'm trying to use the TimescaleDB to return the duration of each state. ChatGPT got me this far but all the durations are coming up 0.

sql
-- Time bucket by hour for utilization
WITH state_changes AS (
  SELECT
    time,
    asset,
    state,
    LAG(state) OVER (PARTITION BY asset ORDER BY time) AS prev_state
  FROM
    public.plates_makino_mills_combined
),
state_durations AS (
  SELECT
    time,
    asset,
    state,
    CASE
      WHEN state != prev_state OR prev_state IS NULL THEN time
    END AS state_start,
    CASE
      WHEN LEAD(state) OVER (PARTITION BY asset ORDER BY time) != state THEN time
    END AS state_end
  FROM
    state_changes
)
SELECT
  time_bucket('1 hour', state_start, 'America/Chicago') AS hour_bucket,
  asset,
  state,
  state_start,
  state_end,
  SUM(EXTRACT(EPOCH FROM state_end - state_start)) AS total_duration
FROM
  state_durations
WHERE
  state_start IS NOT NULL
GROUP BY
  hour_bucket, asset, state, state_start, state_end 
ORDER by
  hour_bucket desc, state, asset;