+2 votes
in Databases by (11.3k points)
PostgreSQL database doesn't support DATEDIFF function. How can I calculate the difference between two given dates?

1 Answer

0 votes
by (11.3k 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]
        upper+=d2    # add days
        
        # 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]
        lower+=d1    # add days
        
        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[0] > ymd_2[0]):
        wrongrange = 1
        ymd_1 = date2.split('-')
        ymd_2 = date1.split('-')
            
    y1 = int(ymd_1[0])
    m1 = int(ymd_1[1])
    d1 = int(ymd_1[2])

    y2 = int(ymd_2[0])
    m2 = int(ymd_2[1])
    d2 = int(ymd_2[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)

...