0

I have some tables in postgresql database with historical data such as following sample: 

 log_id  |       login_time    |       logout_time       
----------+---------------------+---------------------
12885913 | 2016-03-01 05:10:59 | 2016-03-01 09:40:46
12886931 | 2016-03-01 09:41:51 | 2016-03-01 12:44:49
12887276 | 2016-03-01 12:45:54 | 2016-03-01 13:42:18

I want to calculate difference between logout_time of each row with next login_time and save it as login time interval. This table is result of python script that connect to postgresql 9.1 and extract these data from other tables and I want to find a solution with python for this issue.

Farazinux
  • 21
  • 7
  • 1
    this should help after extracting your data http://stackoverflow.com/questions/5259882/subtract-two-times-in-python – danidee Apr 19 '16 at 12:34

1 Answers1

1

You can use window function:

create table log_times (
    log_id int,
    login_time timestamp,
    logout_time timestamp
);

insert into log_times (log_id, login_time, logout_time) 
values 
(12885913, '2016-03-01 05:10:59', '2016-03-01 09:40:46'),
(12886931, '2016-03-01 09:41:51', '2016-03-01 12:44:49'),
(12887276, '2016-03-01 12:45:54', '2016-03-01 13:42:18')
;

select 
    *, 
    login_time - lag(logout_time) over (order by log_id) 
from log_times;

Result: 

  log_id  |     login_time      |     logout_time     | ?column? 
----------+---------------------+---------------------+----------
 12885913 | 2016-03-01 05:10:59 | 2016-03-01 09:40:46 | 
 12886931 | 2016-03-01 09:41:51 | 2016-03-01 12:44:49 | 00:01:05
 12887276 | 2016-03-01 12:45:54 | 2016-03-01 13:42:18 | 00:01:05
(3 rows)
Ivan Burlutskiy
  • 1,605
  • 1
  • 12
  • 22