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!

edited Nov 10 at 18:17

asked Nov 10 at 17:55

Iamadriana

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

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!

edited Nov 10 at 18:17

asked Nov 10 at 17:55

Iamadriana

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

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!

edited Nov 10 at 18:17

asked Nov 10 at 17:55

Iamadriana

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

edited Nov 10 at 18:17

asked Nov 10 at 17:55

Iamadriana

edited Nov 10 at 18:17

asked Nov 10 at 17:55

Iamadriana

edited Nov 10 at 18:17

asked Nov 10 at 17:55

Iamadriana

asked Nov 10 at 17:55

Iamadriana

asked Nov 10 at 17:55

Iamadriana

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

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 |

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

752k34286394

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

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 |

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

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

Name

Required, but never shown

1 Answer
1

active

oldest

votes

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

752k34286394

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

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

752k34286394

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

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

752k34286394

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

752k34286394

answered Nov 10 at 18:16

Gordon Linoff

752k34286394

answered Nov 10 at 18:16

Gordon Linoff

752k34286394

answered Nov 10 at 18:16

Gordon Linoff

752k34286394

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

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'::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

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

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 |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Pfthb