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.

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.

Att.

Winner Martins

CodePudding user response：

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.

CodePudding user response：

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.