Calculate duration from timestamps in one column SQL postgres
up vote
0
down vote
favorite
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!
sql postgresql
asked Nov 10 at 17:55
Iamadriana
13
New contributor
Iamadriana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
0
down vote
favorite
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!
sql postgresql
asked Nov 10 at 17:55
Iamadriana
13
New contributor
Iamadriana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Please explain the logic for defining "browsing episode".
– Gordon Linoff
Nov 10 at 17:57
browsing episode would be the time between 'start' and 'stop' for the same Action_ID
– Iamadriana
Nov 10 at 18:05
@GordonLinoff just edited, it should be there now
– Iamadriana
Nov 10 at 18:18
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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!
sql postgresql
asked Nov 10 at 17:55
Iamadriana
13
New contributor
Iamadriana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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!
sql postgresql
sql postgresql
asked Nov 10 at 17:55
Iamadriana
13
New contributor
Iamadriana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Nov 10 at 17:55
Iamadriana
13
New contributor
Iamadriana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Nov 10 at 18:17
asked Nov 10 at 17:55
Iamadriana
13
New contributor
Iamadriana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Nov 10 at 17:55
Iamadriana
13
asked Nov 10 at 17:55
Iamadriana
13
13
New contributor
Iamadriana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Iamadriana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Iamadriana is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Please explain the logic for defining "browsing episode".
– Gordon Linoff
Nov 10 at 17:57
browsing episode would be the time between 'start' and 'stop' for the same Action_ID
– Iamadriana
Nov 10 at 18:05
@GordonLinoff just edited, it should be there now
– Iamadriana
Nov 10 at 18:18
add a comment |
Please explain the logic for defining "browsing episode".
– Gordon Linoff
Nov 10 at 17:57
browsing episode would be the time between 'start' and 'stop' for the same Action_ID
– Iamadriana
Nov 10 at 18:05
@GordonLinoff just edited, it should be there now
– Iamadriana
Nov 10 at 18:18
Please explain the logic for defining "browsing episode".
– Gordon Linoff
Nov 10 at 17:57
Please explain the logic for defining "browsing episode".
– Gordon Linoff
Nov 10 at 17:57
browsing episode would be the time between 'start' and 'stop' for the same Action_ID
– Iamadriana
Nov 10 at 18:05
browsing episode would be the time between 'start' and 'stop' for the same Action_ID
– Iamadriana
Nov 10 at 18:05
@GordonLinoff just edited, it should be there now
– Iamadriana
Nov 10 at 18:18
@GordonLinoff just edited, it should be there now
– Iamadriana
Nov 10 at 18:18
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
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.
answered Nov 10 at 18:16
Gordon Linoff
742k32285390
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 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'::textafter fixing the column type.
– Gordon Linoff
Nov 10 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 at 19:07
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
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.
answered Nov 10 at 18:16
Gordon Linoff
742k32285390
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 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'::textafter fixing the column type.
– Gordon Linoff
Nov 10 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 at 19:07
add a comment |
up vote
0
down vote
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.
answered Nov 10 at 18:16
Gordon Linoff
742k32285390
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 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'::textafter fixing the column type.
– Gordon Linoff
Nov 10 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 at 19:07
add a comment |
up vote
0
down vote
up vote
0
down vote
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.
answered Nov 10 at 18:16
Gordon Linoff
742k32285390
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.
answered Nov 10 at 18:16
Gordon Linoff
742k32285390
answered Nov 10 at 18:16
Gordon Linoff
742k32285390
answered Nov 10 at 18:16
Gordon Linoff
742k32285390
answered Nov 10 at 18:16
Gordon Linoff
742k32285390
742k32285390
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 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'::textafter fixing the column type.
– Gordon Linoff
Nov 10 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 at 19:07
add a comment |
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 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'::textafter fixing the column type.
– Gordon Linoff
Nov 10 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 at 19:07
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 at 19:48
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 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 at 19:54
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 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 at 19:07
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 at 19:07
add a comment |
Iamadriana is a new contributor. Be nice, and check out our Code of Conduct.
Iamadriana is a new contributor. Be nice, and check out our Code of Conduct.
Iamadriana is a new contributor. Be nice, and check out our Code of Conduct.
Iamadriana is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53241830%2fcalculate-duration-from-timestamps-in-one-column-sql-postgres%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please explain the logic for defining "browsing episode".
– Gordon Linoff
Nov 10 at 17:57
browsing episode would be the time between 'start' and 'stop' for the same Action_ID
– Iamadriana
Nov 10 at 18:05
@GordonLinoff just edited, it should be there now
– Iamadriana
Nov 10 at 18:18