How to get difference of days/months/years (datediff) between two dates?

Simply subtract them:

SELECT ('2015-01-12'::date - '2015-01-01'::date) AS days;

The result:

 days------   11

SELECT  AGE('2012-03-05', '2010-04-01'),  DATE_PART('year', AGE('2012-03-05', '2010-04-01')) AS years,  DATE_PART('month', AGE('2012-03-05', '2010-04-01')) AS months,  DATE_PART('day', AGE('2012-03-05', '2010-04-01')) AS days;

This will give you full years, month, days ... between two dates:

          age          | years | months | days-----------------------+-------+--------+------ 1 year 11 mons 4 days |     1 |     11 |    4

More detailed datediff information.

I spent some time looking for the best answer, and I think I have it.

This sql will give you the number of days between two dates as integer:

SELECT    (EXTRACT(epoch from age('2017-6-15', now())) / 86400)::int

..which, when run today (2017-3-28), provides me with:

?column?------------77

The misconception about the accepted answer:

select age('2010-04-01', '2012-03-05'),   date_part('year',age('2010-04-01', '2012-03-05')),   date_part('month',age('2010-04-01', '2012-03-05')),   date_part('day',age('2010-04-01', '2012-03-05'));

..is that you will get the literal difference between the parts of the date strings, not the amount of time between the two dates.

I.E:

Age(interval)=-1 years -11 mons -4 days;

Years(double precision)=-1;

Months(double precision)=-11;

Days(double precision)=-4;