PostgresSQL query to count time interval inside day period

user_17998135 0
Viewed 67

I have an application that logs three states of some machine. I need to report how long the machine stayed in each state from 00:00:00 until 23:59:59 for each day.

I need a help to build a postgresql query to get entire time interval that some event occur during the day.

For example, as you can see below the data are shown between 23:50:00 previous day until 01:00:00 follwing day.

device_id varchar(50) Value int4 Date_time timestamptz
device1 0 2022-23-04 23:50:00
device1 0 2022-24-04 00:10:00
device1 0 2022-24-04 00:15:00
device1 0 2022-24-04 00:20:00
device1 1 2022-24-04 00:25:00
device1 1 2022-24-04 00:30:00
device1 1 2022-24-04 11:00:00
device1 0 2022-24-04 21:00:00
device1 1 2022-25-04 01:00:00

I am calculating the duration between state changes and I am also inserting in the table and I am simply making the sum but it is giving me the following result:

Total:

  • State = 0 - 04:35
  • State = 1 - 20:35
  • Sum Both = 25:10:00

The query I need should not consider the portion of time that belong to other days that is not 24/04/2021 and must give me:

Total Day 24/04/2022:

  • State = 0 - 03:25
  • State = 1 - 20:35
  • Sum Both = 24:00:00

And in the and of each day I need to have the percentage of time the machine stayed in each state and build a pie chart.

Is there a way to make a query that fits this needs?

Thank You all in advance for the help.

@shawnt00 answer worked. Now I am trying to figure out how to organize the data so the query result come as the following:

device_id state_0 state_1 state_9
device_id timespan_state_0 timespan_state_1 timespan_state_9

For the given example, should be as shown below, I added an second device just to augment the example :)

device_id state_0 state_1 state_9
device1 03:25 20:35 00:00
device2 X Y Z

Att.

Winner Martins

postgresql sql database
2 Answers

I think you just need to use lead()/lag() along with some case expressions to detect the spans across midnight. No join is required:

with data as (
    select *,
        cast(date_trunc('day', Date_time) as date) as dt,
        lag(Date_time) over (partition by device_id order by Date_time) as last_Date_time,
        lead(Date_time) over (partition by device_id order by Date_time) as next_Date_time
    from T
)
select device_id, dt as "date", Value,
    coalesce(sum(
        case when date_trunc('day', next_Date_time) > date_trunc('day', Date_time)
             then date_trunc('day', Date_time + interval '1 day') - Date_time
             else coalesce(next_date_time - Date_time, interval '0 seconds') end
        +
        case when date_trunc('day', last_Date_time) < date_trunc('day', Date_time)
             then Date_time - date_trunc('day', Date_time)
             else interval '0 seconds' end
    ), interval '0 seconds') as timespan2
from data
group by device_id, dt, Value
order by device_id, dt, Value;

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=ab32fee1615b637f9f2f844aa1bf5064

I'm not overly familiar with all the PostGres date functions so there's possibly a slightly cleaner way to get the time calculation.

user_13222680

The query below would work.

select
    std.state,
    sum(case 
            when std.rk = 1 then std.time_diff + std.time_diff_start
            when std.trunc_state_start = std.trunc_state_end then std.time_diff
            when std.trunc_state_start <> std.trunc_state_end then std.time_diff_end
            else std.time_diff
        end)
from
    (
    select
        a.state,
        date_trunc('day', a.date_time) as trunc_state_start,
        date_trunc('day', b.date_time) as trunc_state_end,
        b.date_time - a.date_time as time_diff,
        a.date_time - date_trunc('day', a.date_time) as time_diff_start,
        date_trunc('day', b.date_time) - a.date_time as time_diff_end,
        rank() over(order by a.date_time) rk
    from
        (select ds.*, rank() over(order by date_time) rk from devicestat ds) a
    inner join 
        (select ds.*, rank() over(order by date_time) rk from devicestat ds) b 
    on
        a.rk + 1 = b.rk
    where
        date_trunc('day', a.date_time) = '2022-04-24') std
group by
    std.state;

The joining between itself makes it easy for me to calculate time difference between state start and end time. The rest is how to calculate boundary differences between start and end of the day. I think there are many ways to do that, but this is what came up in my mind.

user_112893860
Related