CODEWITHSUNDEEP
postgresqldate
Jon Snow
Jon Snow

Reputation: 11882

How do I find users created exactly multiple whole months/quarters ago in Postgres SQL?

How do I find all the user records created exactly multiple whole months/quarters ago in Postgres SQL? Is it possible?

In Postgres, you can find a date which is exactly 1 month away from a datetime field, like:

select created_at + interval '1 month' from users limit 1;
?column?          
----------------------------
2016-10-05 17:05:14.811537
(1 row)

If today is 9/27/2016, I want to find all the user records created on 8/27/2016, 7/27/2016, 6/27/2016, ... 1/27/2015, etc, etc. How do I do this in SQL?

Note this isn't simply a comparison of date due to the fact that some months have 31 days while others have 28, 29, 30 days.

If today is 2/28/2015 (non-leap year), I want all the users created on the following dates: 1/28/2015, 1/29/2015, 1/30/2015, 1/31/2015, 12/28/2014, 12/29/2014, 12/30/2014, 12/31/2014, etc etc.

If today is 2/28/2016 (leap year), I want all the users created on the following dates: 1/28/2015, 12/28/2014, 11/28/2014, etc etc. (But not on 1/29/2015, 1/30/2015, 1/31/2015, as those users will be picked up the next day, see below).

If today is 2/29/2016 (leap year), I want all the users created on the following dates: 1/29/2015, 1/30/2015, 1/31/2015, 12/29/2014, 12/30/2014, 12/31/2014, etc etc.

If today is 3/31/2016, I want all the users created on 12/31/2015, 1/31/2016, but not anyone created in February 2016 because they would have been picked on previous days of March 2016.

How do I do the above with quarters instead of months?

Another question related to this is performance. If I create an index on created_at, would a whole table scan be avoided if I do this type of queries?

Thank you.

Upvotes: 1

Views: 141

Answers (1)

T.Z.
T.Z.

Reputation: 2162

Well... If You think of it, You want to compare day number in reality, right? So You should do just that:

select 
    * 
from 
    users 
where 
    date_part('day', created_at) = date_part('day', current_timestamp) 
    OR
    (
    -- Check if this is the last day of month
        extract(month from current_timestamp + '1day'::interval) <> extract(month from current_timestamp)
        AND 
        date_part('day', created_at) > date_part('day', current_timestamp) 
    )
limit 
    1
;

And regarding Your index question - yes.

Inspiration for checking last day of a month taken from here.

Basically, if I still do not get Your requirement, You should be able to easily modify my code to meet it, if You understand it. :)

Upvotes: 1

Related Questions