# PostgreSQL DATEDIFF - Calculate the difference between two dates (Number of days)

PostgreSQL database doesn't support DATEDIFF function. How can I calculate the difference between two given dates?

by (15.2k points)
edited by

You need to create a UDF (user defined function) in any language (e.g. python, perl) to calculate the difference between two dates. Here is an example of such function written in Python. I have not used any built-in module of Python in the code, so the code is a bit longer. You can use the existing modules of Python to shorten the program. On my system, I have PostgreSQL9.5 that supports Python3.3, so the python code is in Python3.0.

CREATE OR REPLACE FUNCTION udatediff (a date, b date)
RETURNS integer
AS \$\$

def leapYear(year):
if (year%4 != 0):
return 1        # common year
elif (year%100 !=0):
return 0        # leap year
elif (year%400 !=0):
return 1        # common year
else:
return 0        # leap year

#-----------------------------------------------------------------------
# Calculate the number of days between given dates
#-----------------------------------------------------------------------

def numOfDays(y1, y2, m1, m2, d1, d2):

leapdays = [31,29,31,30,31,30,31,31,30,31,30,31]
commondays = [31,28,31,30,31,30,31,31,30,31,30,31]

upper = 0    # number of days for smaller date
lower = 0   # number of days for bigger date
# get the number of days due to difference in year
if (y2 >= y1):
for y in range(y1, y2):
if (leapYear(y) == 0):
upper+=366
else:
upper+=365

# get the number of days due to months
for i in range(m2-1):
if (leapYear(y2) == 0):
upper+=leapdays[i]
else:
upper+=commondays[i]

# get the number of days due to months for lower
for i in range(m1-1):
if (leapYear(y1) == 0):
lower+=leapdays[i]
else:
lower+=commondays[i]

return upper - lower

#--------------------------------------------------------------------------------
# calculate the number of days between given dates. date1 is usually smaller
# date whereas date2 is larger.
# if you put date1 > date2, the program will give negative difference.
#--------------------------------------------------------------------------------

def datediff(date1, date2):
wrongrange = 0
ymd_1 = date1.split('-')
ymd_2 = date2.split('-')

if (ymd_1 > ymd_2):
wrongrange = 1
ymd_1 = date2.split('-')
ymd_2 = date1.split('-')

y1 = int(ymd_1)
m1 = int(ymd_1)
d1 = int(ymd_1)

y2 = int(ymd_2)
m2 = int(ymd_2)
d2 = int(ymd_2)

diff = numOfDays(y1, y2, m1, m2, d1, d2)
if (wrongrange == 1):
diff*=-1
return diff

diff = datediff(a,b)
return diff

\$\$ LANGUAGE plpython3u;

To run the above UDF, write your query as follows...

ohdsi_cdm=# select udatediff('2014-12-15','2015-03-10') as diff;
diff
------
85
(1 row)

If you provide first date larger than the second date, the select query will give -ve answer.

ohdsi_cdm=# select udatediff('2015-03-10','2014-12-15') as diff;
diff
------
-85
(1 row)