I am looking for a way to implement the SQLServer-function datediff in PostgreSQL. That is, this function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified start date and end date.
datediff(dd, '2010-04-01', '2012-03-05') = 704 // 704 changes of day in this interval
datediff(mm, '2010-04-01', '2012-03-05') = 23 // 23 changes of month
datediff(yy, '2010-04-01', '2012-03-05') = 2 // 2 changes of year
I know I could do 'dd' by simply using subtraction, but any idea about the other two?
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.