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

Postgresql – SQL How to determine the duration of the action based on the current and next event?

I have a table with two columns: the action start time and the name of action.

time action
2023-07-27 04:52:00.000 running
2023-07-27 04:55:00.000 walking
2023-07-27 04:59:00.000 walking
2023-07-27 05:01:00.000 sitting
2023-07-27 05:06:00.000 walking
2023-07-27 05:10:00.000 running

I need to know the duration of the action: start and end time. The start time is the time of the action, which is already known.
But how to know the end time? That is, the beginning of the next action different from the current one?

Example for the action "walking"

time_start time_end
2023-07-27 04:55:00.000 2023-07-27 05:01:00.000
2023-07-27 05:06:00.000 2023-07-27 05:10:00.000
Tags:

2

Answers


  1. 0

    Using the difference in row numbers method we can try the following:

    WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY time) rn1,
              ROW_NUMBER() OVER (PARTITION BY action ORDER BY time) rn2
    FROM yourTable
)

SELECT MIN(time) AS time_start,
       MAX(time) AS time_end,
       action
FROM cte
GROUP BY rn1 - rn2, action
ORDER BY MIN(time);

    screen capture from demo link below

    Demo

    Login or Signup to reply.
  2. 0

    I think you could use the lag function to achieve your goal.

    select 
    action,
    time as time_start,
    lag(time, -1) over(order by time) as time_end
from <table_name>

    In this case the time_end for the last row will be equal to NULL, which I think is correct, as it isn’t known yet.

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

Back To Top
Search