Styjun

How is the Team Scooby Doo funded?

Double it your way

Where can I get an anonymous Rav Kav card issued?

How do you build a Dominant 7th chord?

Will replacing a fake visa with a different fake visa cause me problems when applying for a legal study permit?

Can the UK veto its own extension request?

What is the standard practice in Constraint Programming modeling?

„nichts wie raus hier“ - explanation based on the literal meaning?

Are Democrats more likely to believe Astrology is a science?

Might have gotten a coworker sick, should I address this?

Is the union of a chain of elementary embeddings elementary?

My research paper filed as a patent in China by my Chinese supervisor without me as inventor

How do email clients "send later" without storing a password?

Can a magnet rip protons from a nucleus?

What's the biggest organic molecule that could have a smell?

Insert str into larger str in the most pythonic way

A Little Riddle

What does "synoptic" mean in avionics?

How can I fix a framing mistake so I can drywall?

How can I maximize the impact of my charitable donations?

Which ping implementation is Cygwin using?

Introductory textbook on geometry of hyperbolic space

How would a village use its river that it shares with another village downstream?

I asked for a graduate student position from a professor. He replied "welcome". What does that mean?

how to get all the contiguous interval of all IDs in a relation

How can I prevent SQL injection in PHP?How to return only the Date from a SQL Server DateTime datatypeGet list of all tables in Oracle?How do you get a timestamp in JavaScript?How do I get the current date in JavaScript?How do I UPDATE from a SELECT in SQL Server?How to format a JavaScript dateWhat are the options for storing hierarchical data in a relational database?Get top 1 row of each groupHow to exit from PostgreSQL command line utility: psql

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

So my relation is simple: relation (ID, Date), which ID is not unique and not necessarily in any order. Each ID has a date (same ID can have the same date). My problem is to find the longest interval between a date and its NEXT date of all IDs.

So if the table is like this:

 ID | Date
--------+------------
 100 | 2015-06-20
 100 | 2015-01-21
 100 | 2016-04-23

the expected output will be

ID | interval
--------+------------
 100 | (2016-04-23 - 2015-06-20)

or if all date the ID has are the same:

 ID | Date
--------+------------
 100 | 2016-04-23
 100 | 2016-04-23
 100 | 2016-04-23

the expected output should be

ID | interval
--------+------------
 100 | 0

this is for a single ID, in my relation, there are 100 IDs are together

sql postgresql date intervals

share|improve this question

edited Mar 28 at 9:02

a_horse_with_no_name

332k5555 gold badges515515 silver badges609609 bronze badges

asked Mar 28 at 7:40

Qingzhuoran GuoQingzhuoran Guo

111 bronze badge

add a comment | 

0

So my relation is simple: relation (ID, Date), which ID is not unique and not necessarily in any order. Each ID has a date (same ID can have the same date). My problem is to find the longest interval between a date and its NEXT date of all IDs.

So if the table is like this:

 ID | Date
--------+------------
 100 | 2015-06-20
 100 | 2015-01-21
 100 | 2016-04-23

the expected output will be

ID | interval
--------+------------
 100 | (2016-04-23 - 2015-06-20)

or if all date the ID has are the same:

 ID | Date
--------+------------
 100 | 2016-04-23
 100 | 2016-04-23
 100 | 2016-04-23

the expected output should be

ID | interval
--------+------------
 100 | 0

this is for a single ID, in my relation, there are 100 IDs are together

sql postgresql date intervals

share|improve this question

edited Mar 28 at 9:02

a_horse_with_no_name

332k5555 gold badges515515 silver badges609609 bronze badges

asked Mar 28 at 7:40

Qingzhuoran GuoQingzhuoran Guo

111 bronze badge

add a comment | 

So my relation is simple: relation (ID, Date), which ID is not unique and not necessarily in any order. Each ID has a date (same ID can have the same date). My problem is to find the longest interval between a date and its NEXT date of all IDs.

So if the table is like this:

 ID | Date
--------+------------
 100 | 2015-06-20
 100 | 2015-01-21
 100 | 2016-04-23

the expected output will be

ID | interval
--------+------------
 100 | (2016-04-23 - 2015-06-20)

or if all date the ID has are the same:

 ID | Date
--------+------------
 100 | 2016-04-23
 100 | 2016-04-23
 100 | 2016-04-23

the expected output should be

ID | interval
--------+------------
 100 | 0

this is for a single ID, in my relation, there are 100 IDs are together

sql postgresql date intervals

share|improve this question

edited Mar 28 at 9:02

a_horse_with_no_name

332k5555 gold badges515515 silver badges609609 bronze badges

asked Mar 28 at 7:40

Qingzhuoran GuoQingzhuoran Guo

111 bronze badge

 ID | Date
--------+------------
 100 | 2015-06-20
 100 | 2015-01-21
 100 | 2016-04-23

ID | interval
--------+------------
 100 | (2016-04-23 - 2015-06-20)

 ID | Date
--------+------------
 100 | 2016-04-23
 100 | 2016-04-23
 100 | 2016-04-23

ID | interval
--------+------------
 100 | 0

share|improve this question

edited Mar 28 at 9:02

a_horse_with_no_name

332k5555 gold badges515515 silver badges609609 bronze badges

asked Mar 28 at 7:40

Qingzhuoran GuoQingzhuoran Guo

111 bronze badge

share|improve this question

edited Mar 28 at 9:02

a_horse_with_no_name

332k5555 gold badges515515 silver badges609609 bronze badges

asked Mar 28 at 7:40

Qingzhuoran GuoQingzhuoran Guo

111 bronze badge

edited Mar 28 at 9:02

a_horse_with_no_name

332k5555 gold badges515515 silver badges609609 bronze badges

edited Mar 28 at 9:02

a_horse_with_no_name

332k5555 gold badges515515 silver badges609609 bronze badges

asked Mar 28 at 7:40

Qingzhuoran GuoQingzhuoran Guo

111 bronze badge

asked Mar 28 at 7:40

Qingzhuoran GuoQingzhuoran Guo

111 bronze badge

2 Answers
2

active

oldest

votes

0

I think this query will be useful for you:

select t.id,
 case
 when t.lower != t.upper then '(' || t.lower || ' - ' || t.upper || ')'
 else '0' end
from (select
 r.id,
 min(r.date) as lower,
 max(r.date) as upper
 from relation r
 group by r.id) t;

We use a subquery to find lower and upper boundaries for each ID. After that we check lower and upper boundaries when they equals make formatted string else out zero.

share|improve this answer

edited Mar 28 at 7:57

answered Mar 28 at 7:51

HAYMbl4HAYMbl4

9571010 silver badges2525 bronze badges

add a comment | 

0

I hope this is what you are looking for

 WITH x AS 
(
 SELECT id, _date, lead_date, EXTRACT(epoch FROM age(lead_date,_date))/(3600*24) AS age
 FROM
 (
 SELECT *, lead(_date) over(PARTITION BY id ORDER BY _date ) lead_date
 from table_log
 order by id, _date
 ) as z
 WHERE lead_date IS NOT NULL
 ORDER BY 4 DESC
 )
SELECT DISTINCT id ,
 (SELECT age FROM x WHERE x.id = t1.id ORDER BY age DESC LIMIT 1)
 FROM table_log t1

Here i have used windows function to get the next date to determine the duration between 2 entries. with Postgres Recursive query you can re-use the original query with windows function.

I have used DISTINCT from the log table, but you can also directly use the table where you store the IDs.

share|improve this answer

edited Mar 28 at 15:54

a_horse_with_no_name

332k5555 gold badges515515 silver badges609609 bronze badges

answered Mar 28 at 15:44

Anand AAnand A

7733 bronze badges

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',
autoActivateHeartbeat: false,
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/4.0/"u003ecc by-sa 4.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

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55392373%2fhow-to-get-all-the-contiguous-interval-of-all-ids-in-a-relation%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

I think this query will be useful for you:

select t.id,
 case
 when t.lower != t.upper then '(' || t.lower || ' - ' || t.upper || ')'
 else '0' end
from (select
 r.id,
 min(r.date) as lower,
 max(r.date) as upper
 from relation r
 group by r.id) t;

We use a subquery to find lower and upper boundaries for each ID. After that we check lower and upper boundaries when they equals make formatted string else out zero.

share|improve this answer

edited Mar 28 at 7:57

answered Mar 28 at 7:51

HAYMbl4HAYMbl4

9571010 silver badges2525 bronze badges

add a comment | 

0

I think this query will be useful for you:

select t.id,
 case
 when t.lower != t.upper then '(' || t.lower || ' - ' || t.upper || ')'
 else '0' end
from (select
 r.id,
 min(r.date) as lower,
 max(r.date) as upper
 from relation r
 group by r.id) t;

We use a subquery to find lower and upper boundaries for each ID. After that we check lower and upper boundaries when they equals make formatted string else out zero.

share|improve this answer

edited Mar 28 at 7:57

answered Mar 28 at 7:51

HAYMbl4HAYMbl4

9571010 silver badges2525 bronze badges

add a comment | 

I think this query will be useful for you:

select t.id,
 case
 when t.lower != t.upper then '(' || t.lower || ' - ' || t.upper || ')'
 else '0' end
from (select
 r.id,
 min(r.date) as lower,
 max(r.date) as upper
 from relation r
 group by r.id) t;

We use a subquery to find lower and upper boundaries for each ID. After that we check lower and upper boundaries when they equals make formatted string else out zero.

share|improve this answer

edited Mar 28 at 7:57

answered Mar 28 at 7:51

HAYMbl4HAYMbl4

9571010 silver badges2525 bronze badges

select t.id,
 case
 when t.lower != t.upper then '(' || t.lower || ' - ' || t.upper || ')'
 else '0' end
from (select
 r.id,
 min(r.date) as lower,
 max(r.date) as upper
 from relation r
 group by r.id) t;

share|improve this answer

edited Mar 28 at 7:57

answered Mar 28 at 7:51

HAYMbl4HAYMbl4

9571010 silver badges2525 bronze badges

answered Mar 28 at 7:51

HAYMbl4HAYMbl4

9571010 silver badges2525 bronze badges

answered Mar 28 at 7:51

HAYMbl4HAYMbl4

9571010 silver badges2525 bronze badges

0

I hope this is what you are looking for

 WITH x AS 
(
 SELECT id, _date, lead_date, EXTRACT(epoch FROM age(lead_date,_date))/(3600*24) AS age
 FROM
 (
 SELECT *, lead(_date) over(PARTITION BY id ORDER BY _date ) lead_date
 from table_log
 order by id, _date
 ) as z
 WHERE lead_date IS NOT NULL
 ORDER BY 4 DESC
 )
SELECT DISTINCT id ,
 (SELECT age FROM x WHERE x.id = t1.id ORDER BY age DESC LIMIT 1)
 FROM table_log t1

Here i have used windows function to get the next date to determine the duration between 2 entries. with Postgres Recursive query you can re-use the original query with windows function.

I have used DISTINCT from the log table, but you can also directly use the table where you store the IDs.

share|improve this answer

edited Mar 28 at 15:54

a_horse_with_no_name

332k5555 gold badges515515 silver badges609609 bronze badges

answered Mar 28 at 15:44

Anand AAnand A

7733 bronze badges

add a comment | 

0

I hope this is what you are looking for

 WITH x AS 
(
 SELECT id, _date, lead_date, EXTRACT(epoch FROM age(lead_date,_date))/(3600*24) AS age
 FROM
 (
 SELECT *, lead(_date) over(PARTITION BY id ORDER BY _date ) lead_date
 from table_log
 order by id, _date
 ) as z
 WHERE lead_date IS NOT NULL
 ORDER BY 4 DESC
 )
SELECT DISTINCT id ,
 (SELECT age FROM x WHERE x.id = t1.id ORDER BY age DESC LIMIT 1)
 FROM table_log t1

Here i have used windows function to get the next date to determine the duration between 2 entries. with Postgres Recursive query you can re-use the original query with windows function.

I have used DISTINCT from the log table, but you can also directly use the table where you store the IDs.

share|improve this answer

edited Mar 28 at 15:54

a_horse_with_no_name

332k5555 gold badges515515 silver badges609609 bronze badges

answered Mar 28 at 15:44

Anand AAnand A

7733 bronze badges

add a comment | 

I hope this is what you are looking for

 WITH x AS 
(
 SELECT id, _date, lead_date, EXTRACT(epoch FROM age(lead_date,_date))/(3600*24) AS age
 FROM
 (
 SELECT *, lead(_date) over(PARTITION BY id ORDER BY _date ) lead_date
 from table_log
 order by id, _date
 ) as z
 WHERE lead_date IS NOT NULL
 ORDER BY 4 DESC
 )
SELECT DISTINCT id ,
 (SELECT age FROM x WHERE x.id = t1.id ORDER BY age DESC LIMIT 1)
 FROM table_log t1

Here i have used windows function to get the next date to determine the duration between 2 entries. with Postgres Recursive query you can re-use the original query with windows function.

I have used DISTINCT from the log table, but you can also directly use the table where you store the IDs.

share|improve this answer

edited Mar 28 at 15:54

a_horse_with_no_name

332k5555 gold badges515515 silver badges609609 bronze badges

answered Mar 28 at 15:44

Anand AAnand A

7733 bronze badges

 WITH x AS 
(
 SELECT id, _date, lead_date, EXTRACT(epoch FROM age(lead_date,_date))/(3600*24) AS age
 FROM
 (
 SELECT *, lead(_date) over(PARTITION BY id ORDER BY _date ) lead_date
 from table_log
 order by id, _date
 ) as z
 WHERE lead_date IS NOT NULL
 ORDER BY 4 DESC
 )
SELECT DISTINCT id ,
 (SELECT age FROM x WHERE x.id = t1.id ORDER BY age DESC LIMIT 1)
 FROM table_log t1

share|improve this answer

edited Mar 28 at 15:54

a_horse_with_no_name

332k5555 gold badges515515 silver badges609609 bronze badges

answered Mar 28 at 15:44

Anand AAnand A

7733 bronze badges

edited Mar 28 at 15:54

a_horse_with_no_name

332k5555 gold badges515515 silver badges609609 bronze badges

edited Mar 28 at 15:54

a_horse_with_no_name

332k5555 gold badges515515 silver badges609609 bronze badges

answered Mar 28 at 15:44

Anand AAnand A

7733 bronze badges

answered Mar 28 at 15:44

Anand AAnand A

7733 bronze badges