PostgreSQL. Find max date range in records

Question

I have this records:

id | user_id |    date   
 1 |    1    | 2015-01-01
 2 |    1    | 2015-01-05
 3 |    2    | 2014-01-01
 4 |    2    | 2014-01-30
 5 |    3    | 2014-01-25
 6 |    3    | 2014-02-28

I want to select date ranges to each user, sorted by date range length.

So, in the results I want to get this:

 user_id |  min_date   |  max_date
    2    |  2014-01-01 | 2014-01-30
    3    |  2014-01-25 | 2014-02-28
    1    |  2015-01-01 | 2015-01-05

Is it possible to do with sql query?

http://sqlfiddle.com/#!15/f958b

Philip Couling · Accepted Answer · 2015-08-10 08:59:46Z

2

I must be missing something obveous is this not just a simple group by?

   SELECT user_id, 
          min(date), 
          max(date) 
     FROM test_table
 GROUP BY user_id 
 ORDER BY max(date) - min(date) DESC, 
          user_id;

edited Aug 10, 2015 at 8:59

answered Aug 10, 2015 at 8:46

Philip Couling

15.2k8 gold badges73 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tom Over a year ago

Thanks for your answer. Please, check results of your query here sqlfiddle.com/#!15/f958b/2 user_id=2 should be on the first row (because of 30 days date range), the second row should be user_id=3. It seems like your query is not working like this

Philip Couling Over a year ago

I dont have a postgres server to hand at this second, but you can add a desc if that helps. I've edited the answer.

user330315 Over a year ago

@couling: check the sqlfiddle link: you do have a Postgres server at hand there

Philip Couling Over a year ago

@a_horse_with_no_name oh I see. that's not just a pastebin alternative ;-) Ah well. Yes adding desc works.

Erwin Brandstetter Over a year ago

@AlexeyLisikhin: If any of the columns can be NULL, add NULLS LAST to respective ORDER BY items.

realnumber3012 · Accepted Answer · 2015-08-10 09:00:19Z

1

SELECT * FROM
(
SELECT user_id, MIN(date) AS min_date, 
MAX(date) AS max_date FROM test_table 
GROUP BY user_id
) t
ORDER BY max_date- min_date DESC

edited Aug 10, 2015 at 9:00

user330315

answered Aug 10, 2015 at 8:30

realnumber3012

1,0626 silver badges10 bronze badges

3 Comments

Tom Over a year ago

sqlfiddle.com/#!15/eb51d Thanks. But I need order by date range. It can be date range is bigger one, but end date of this date range not latest date in the table

Tom Over a year ago

New answer is working, thank you! Please write it here (not just put a link), and I will accept it.

Tom Over a year ago

I tested this code a little more, it is not working like needed :( Please, check this data examples. user_id=3 should be on the second row, but it is on the third row sqlfiddle.com/#!15/f958b/1

Collectives™ on Stack Overflow

PostgreSQL. Find max date range in records

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related