Question 259216 in PostgreSQL Question posted in
The official documentation can be found here.

timescaledb or postgresql aggregation by getting time difference between two events

I collect device ON/OFF events in a timeseries table. Following is a sample data.

time_stamp state
2023-10-04 10:05:53 0
2023-10-04 10:15:58 1
2023-10-04 10:30:59 0
2023-10-04 10:40:00 1
2023-10-04 10:55:01 0
2023-10-04 11:05:03 1
2023-10-04 11:35:36 0
2023-10-04 11:40:39 1
2023-10-04 11:55:07 0

I need to aggregate how much time the state remained 1 within each hour. So for hour 10 in the sample below:

  • 10:00 to 10:05 – ON = 5 mins *it was turned on before 10:00 but we only count from top of the hour
  • 10:05 to 10:15 – OFF = 10 mins
  • 10:15 to 10:30 – ON = 15 mins
  • 10:30 to 10:40 – OFF = 10 mins
  • 10:40 to 10:55 – ON = 15 mins
  • 10:55 – 11:00 – OFF = 5 mins

So, out of 60 mins between 10:00 and 11:00, it was ON for 5 + 15 + 15 = 35 mins and OFF for 25 mins.

And similarly for the hour of 11:00 to 12:00, it is ON for 45 mins.

The outcome of the aggregation using the data sample.

time_stamp minutesON
2023-10-04 10:00:00 35
2023-10-04 11:00:00 45

The question is how can I achieve this using a postgresql query that I can use to write a continuous aggregate in timescale db.

Tags:

2

Answers


  1. 0

    looks like you are looking for this

    SELECT measure_id,
    time_bucket('15 min'::interval, ts) as bucket,
    delta(
        counter_agg(ts, val)
    )
FROM example
GROUP BY measure_id, time_bucket('15 min'::interval, ts);
    Login or Signup to reply.
  2. 0

    Also, the state_agg allow you to collect interpolated duration in between states.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search