CODEWITHSUNDEEP
sqlpostgresql
user3824666
user3824666

Reputation: 333

How to find the next date PostgreSQL

I have the following table "UserState":

_id___|_ctime____________________________|_state__________|_userid
 1    | 2014-06-09 05:57:07.033334+00    | Ready          | 1
 2    | 2014-06-09 05:53:35.243864+00    | Busy           | 1
 3    | 2014-06-09 05:51:39.412722+00    | Ready          | 1
 4    | 2014-06-09 05:48:48.09651+00     | logged out     | 1

*ctime means the time of getting the new status by user. This table means, that I have only the start time of state, but don't know to end time. The end time of the state is the start time of another one.

And I need an SQL query, that will provide me the following data for every state:

userid | state | ctime | endtime considering the endtime as the starttime(ctime) of a next state

It's not possible to add new columns to the table "UserState". But it's possible, that user with (id = 1) may have state 'Ready' after the same state 'Ready', such states must be grouped into 1 row.

Upvotes: 0

Views: 3259

Answers (2)

user330315
user330315

Reputation:

A window function will do this:

select userid,
       state,
       ctime as start_time, 
       lead(ctime) over (partition by userid order by ctime) as end_time
from userstate;

If you are not used to window functions, please see the Postgres manual:
http://www.postgresql.org/docs/current/static/tutorial-window.html

Upvotes: 2

pozs
pozs

Reputation: 36214

This is exactly, what window functions can do. This case, you can use the lead() function.

Upvotes: 0

Related Questions