PostgreSQL: days/months/years between two dates

postgresql date
gefei picture gefei · Jul 24, 2013 · Viewed 183.9k times · Source

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 startdate and enddate.

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 substraction, but any idea about the other two?

Answer

mehdi picture mehdi · Jun 12, 2015

Simply subtract them:

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

The result:

 days
------
   11

Related questions

How to compare dates in datetime fields in Postgresql?

I have been facing a strange scenario when comparing dates in postgresql(version 9.2.4 in windows). I have a column in my table say update_date with type 'timestamp without timezone'. Client can search over this field with only date (i.…

Read More
Postgresql query between date ranges

I am trying to query my postgresql db to return results where a date is in certain month and year. In other words I would like all the values for a month-year. The only way i've been able to do …

Read More
PostgreSQL: how to convert from Unix epoch to date?

The statement gives me the date and time. How could I modify the statement so that it returns only the date (and not the time)? SELECT to_timestamp( TRUNC( CAST( epoch_ms AS bigint ) / 1000 ) );

Read More