PostgreSQL, checking date relative to "today"


Sql Problem Overview

Was wondering if someone could assist with some Postgres. I have a table which has a column called mydate which is a postgres date type. I want to do something like:

SELECT * FROM MyTable WHERE mydate > [Today-1year]

I've never used Postgres before and I'm sure I just need to know the name of some functions- I'll gladly look up the reference myself. Can anyone point me in the right direction?


Sql Solutions

Solution 1 - Sql

select * from mytable where mydate > now() - interval '1 year';

If you only care about the date and not the time, substitute current_date for now()

select * from mytable where mydate > current_date - interval '1 year';

Solution 2 - Sql

I think this will do it:

SELECT * FROM MyTable WHERE mydate > now()::date - 365;

Solution 3 - Sql

This should give you the current date minus 1 year:

select now() - interval '1 year';

Solution 4 - Sql

You could also check using the age() function

select * from mytable where age( mydate, now() ) > '1 year';

age() wil return an interval.

For example age( '2015-09-22', now() ) will return -1 years -7 days -10:56:18.274131

See postgresql documentation


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionJosephView Question on Stackoverflow
Solution 1 - SqlPaul TomblinView Answer on Stackoverflow
Solution 2 - SqlAlex HowanskyView Answer on Stackoverflow
Solution 3 - SqlcoderajView Answer on Stackoverflow
Solution 4 - SqlDocView Answer on Stackoverflow