0

I have a database in Postgres SQL with information on users browsing online content. I would like to calculate the duration of browsing for each browsing episode (episode is one start and one stop action for the same Action_ID) and then to see it per user and per day. I can identify users by User_ID and by Action column to see when browsing started and ended.

I am therefore stuck with figuring out how to instead of having two rows per Action (with start and stop time) make one row per browsing Action with duration time only? 

Date_time       User_ID Device_ID   Action  Action_ID  Channel_ID
22/07/2016 00:10    id2  pc      stop   123qwe      gn3
22/07/2016 00:10    id5  pc      start  345ert      tm6
22/07/2016 00:10    id1  mob     stop   567rfg      uy6
22/07/2016 00:12    id1  mob     start  567rfg      uy6
22/07/2016 00:13    d3   pc      stop   987yhn      io9

I've tried Calculate duration between momentjs timestamps in UTC and Calculate duration between timestamps in one column already and I've done some googling, but I didn't get any closer.

Thanks!

Iamadriana
  • 11
  • 4

1 Answers1

0

You can get the dates as:

select date_trunc('day', date_time) as theday, user_id, action_id,
       min(date_time) filter (where action = 'start') as start_time,
       max(date_time) filter (where action = 'stop') as stop_time
from t
group by theday, user_id, action_id;

The duration is then the difference between these -- but the exactly logic depends on how you want to see the duration.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I tried this solution and it gave me an Error: "function date_trunc(unknown, character varying) does not exist. No function matches the given name and argument types." I thought this is caused because of the 'date_time' column data type and I tried changing it to timestamp with CAST. This didn't work - is that because there are no seconds in the 'date_time' column? – Iamadriana Nov 10 '18 at 19:48
  • Fix your data so the date is stored as a *datetime* and not a string. `date_trunc()` works fine in Postgres. You could try `'day'::text` *after* fixing the column type. – Gordon Linoff Nov 10 '18 at 19:54
  • Thanks Gordon, I fixed the datatype and I split my data into 2 separated columns instead : one with date and one with time. Thus, the above solution is not calculating the desired output of duration time of action from start to stop in minutes. – Iamadriana Nov 11 '18 at 19:07