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

Postgresql – SQL – Find cycle and duration

I search a solution for my problem in SQL (postgresql).

I have a logs table with the following columns:

deviceId id code timestamp
device1 1 12 1672597471000
device1 2 11 1672597471001
device1 3 8 1672597471002
device1 4 2 1672597471003
device1 5 9 1672597471004
device1 6 9 1672597471005
device1 7 4 1672597471006
device1 8 8 1672597471007
device1 9 9 1672597471008
device2 1 8 1672597471000
device2 2 9 1672597471010
device2 3 12 1672597471050
device2 4 8 1672597471100
device2 5 9 1672597471130

I search one query (or more) to find the time elapsed between two codes (8 and 9 for example).
The output of SQL query will be:

deviceId elapsed time
device1 2
device1 1
device2 10
device2 30

I would really appreciate if anyone can suggest some ideas how to solve this problem.

I tried with lead function but i can’t reset time between each sequence (8 and 9) and ignore unique 8 or 9. In short, i’m stuck and I don’t know if it’s possible in SQL.

Tags:

2

Answers


  1. 0

    This can be accomplished by using the window function lag() to obtain the previous row of the current row, followed by subtraction to obtain the elapsed time between 8 and 9 using the condition where code = 9 and lag_code = 8:

     with cte as (
  select *, 
            lag(timestamp) over (partition by deviceid order by timestamp) as lag_timestamp,
            lag(code) over (partition by deviceid order by timestamp) as lag_code
  from mytable
  where code between 8 and 9
)
select deviceId, timestamp - lag_timestamp as elapsed_time
from cte
where code = 9 and lag_code = 8

    Result :

    deviceid elapsed_time
    device1 2
    device1 1
    device2 10
    device2 30

    Demo here

    Login or Signup to reply.
  2. 0

    Use this simple query to produce the result you need (and test it on fiddle) :

    select a.deviceId, (b.timestamp - a.timestamp) as elapsed_time
from logs a join logs b
on b.deviceid = a.deviceid
and b.id = (select min(id)
            from logs b
            where b.deviceid = a.deviceid
            and b.id > a.id
            and code = 9)
where a.code = 8;

    and it gives:

     deviceid | elapsed_time 
----------+--------------
 device1  |            2
 device1  |            1
 device2  |           10
 device2  |           30
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search