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...
If you provide first date larger than the second date, the select query will give -ve answer.
