0

I need to subtract two dates from each other (start_time(n+1)-end_time(n)) which are in two different rows and columns by partitioning with id. Look at the table below:

row |id|    Start_time       |      end_time
----+--+---------------------+------------------------
 1  |14|"2012-06-16 21:43:00"|"2012-06-16 23:54:00"
 2  |14|"2012-06-19 13:09:00"|"2012-06-19 23:59:00"
 3  |21|"2016-04-12 14:46:00"|"2016-04-25 13:55:00"
 4  |21|"2016-04-20 09:35:00"|"2016-04-20 22:00:00"
 5  |24|"2011-09-19 19:20:00"|"2011-09-19 22:15:00"
 6  |24|"2011-09-20 19:01:00"|"2011-09-22 14:05:00"
 7  |30|"2009-10-21 07:25:00"|"2009-10-21 10:59:00"
 8  |30|"2009-10-27 16:01:00"|"2009-11-10 16:00:00"
 9  |30|"2009-10-28 08:13:00"|"2009-10-28 23:59:00"
10  |36|"2015-11-23 12:21:00"|"2015-11-23 15:19:00"

Example: for id=14, I need to subtract end_time in row 1 from start_time in row 2, and store the result in a new column. My main purpose is that to find the time difference between each measurement for every ids. Is it possible at all?

My preferred results would be like diff column:

row |id|    Start_time       |      end_time       |   diff   
----+--+---------------------+---------------------+------------
 1  |14|"2012-06-16 21:43:00"|"2012-06-16 23:54:00"| 
 2  |14|"2012-06-19 13:09:00"|"2012-06-19 23:59:00"|"2 days 13:15:00"
 3  |21|"2016-04-12 14:46:00"|"2016-04-25 13:55:00"|
 4  |21|"2016-04-20 09:35:00"|"2016-04-20 22:00:00"|"-5 days -04:20:00"
 5  |24|"2011-09-19 19:20:00"|"2011-09-19 22:15:00"|
 6  |24|"2011-09-20 19:01:00"|"2011-09-22 14:05:00"|"20:46:00"
Improve this question
6
  • 4
    use lag(): postgresql.org/docs/current/static/tutorial-window.html Commented Oct 23, 2017 at 9:14
  • you can fine same ans here stackoverflow.com/questions/23062402/… Commented Oct 23, 2017 at 9:16
  • @a_horse_with_no_name please see my updated question. I need to calculate start_time (n+1) - end_time(n) for every id, which n is number of row. Commented Oct 23, 2017 at 9:39
  • @AkankhaAhmed please see the results that I am looking for. Commented Oct 23, 2017 at 9:41
  • 1
    That's exactly what lag() is for: start_time - lag(end_time) over (order by start_time), or using lead() lead(start_time) over (...) - end_time` Commented Oct 23, 2017 at 9:43
Related questions
4
Date subtraction in postgres
0
Postgres date subtraction in a query
6
Subtracting two dates using PostgreSQL
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.