I'm trying to Implement Netezza AGE function in Redshift as a UDF. I can able to get the correct answer in Python (Spyder IDE - Py 3.6) but when I execute it in Redshift as UDF, it gives me incorrect output.
I've tried to execute as select AGE_UDF('1994-04-04 20:10:52','2018-09-24 11:31:05'); in Redshift.
Here is the code used in RS UDF.
CREATE OR REPLACE FUNCTION AGE_UDF (START_DATE TIMESTAMP, END_DATE TIMESTAMP)
RETURNS varchar(100)
stable
AS $$
from datetime import datetime
from dateutil import relativedelta
START_DATE = datetime.strptime(START_DATE, '%Y-%m-%d %H:%M:%S')
END_DATE = datetime.strptime(END_DATE, '%Y-%m-%d %H:%M:%S')
difference = relativedelta.relativedelta(END_DATE, START_DATE)
years = difference.years
months = difference.months
days = difference.days
hours = difference.hours
minutes = difference.minutes
seconds = difference.seconds
age=''
if years == 0:
age=''
elif years == 1:
age+=str(years)+' year '
else:
age+=str(years)+' years '
if months == 0:
age+=''
elif months == 1:
age+=str(months)+' mon '
else:
age+=str(months)+' mons '
if days == 0:
age+=''
elif days == 1:
age+=str(days)+' day '
else:
age+=str(days)+' days '
age+=str(hours)+':'+str(minutes)+':'+str(seconds)
return age
$$ language plpythonu;
Output in RS: -8809.15:20:13
Here is the Code used in Python (3.6).
from datetime import datetime
from dateutil import relativedelta
START_DATE = '1994-04-04 20:10:52'
START_DATE = datetime.strptime(START_DATE, '%Y-%m-%d %H:%M:%S')
END_DATE = '2018-09-24 11:31:05'
END_DATE = datetime.strptime(END_DATE, '%Y-%m-%d %H:%M:%S')
difference = relativedelta.relativedelta(END_DATE, START_DATE)
years = difference.years
months = difference.months
days = difference.days
hours = difference.hours
minutes = difference.minutes
seconds = difference.seconds
age=''
if years == 0:
age=''
elif years == 1:
age+=str(years)+' year '
else:
age+=str(years)+' years '
if months == 0:
age+=''
elif months == 1:
age+=str(months)+' mon '
else:
age+=str(months)+' mons '
if days == 0:
age+=''
elif days == 1:
age+=str(days)+' day '
else:
age+=str(days)+' days '
age+=str(hours)+':'+str(minutes)+':'+str(seconds)
print(age)
Output in Python: 24 years 5 mons 19 days 15:20:13
EDIT:
I found the way to achieve the Netezza functionality and I've pasted here.
Still I'm Expecting an another efficient way !!! Cheers !!!
Thanks for the Support and Suggestions !!!
No Python needed. Here's a SQL UDF that encapsulates the logic. You will need to extend it if the units plural is important to you (mons vs mon).
/*
Postgres AGE() Function
*/
CREATE OR REPLACE FUNCTION f_postgres_age(TIMESTAMP, TIMESTAMP)
RETURNS VARCHAR(64)
STABLE AS $$
-- Input: '1994-04-04 20:10:52', '2018-09-24 11:31:05'
-- Output: 24 years 5 mons 19 days 15:20:13
-- Input: '1994-10-04 20:10:52', '2019-06-12 11:31:05'
-- Output: 24 years 8 mons 7 days 15:20:13
-- Check: SELECT '1994-10-04 20:10:52'::TIMESTAMP
-- + INTERVAL '24 years' + INTERVAL '8 months' + INTERVAL '7 days'
-- + INTERVAL '15 hours' + INTERVAL '20 minutes' + INTERVAL '13 seconds';
-- Result: 2019-06-12 11:31:05
SELECT CASE WHEN DATEDIFF(year, DATE_TRUNC('year', $1)
, DATE_TRUNC('year', CASE WHEN DATEPART(month, $1) > DATEPART(month, $2)
THEN $2 - INTERVAL '1 Year' ELSE $2 END)) > 0
THEN DATEDIFF(year, DATE_TRUNC('year', $1)
, DATE_TRUNC('year', CASE WHEN DATEPART(month, $1) > DATEPART(month, $2)
THEN $2 - INTERVAL '1 Year' ELSE $2 END)) || ' years '
ELSE '' END
|| CASE WHEN ABS( DATEDIFF(month, DATE_TRUNC('month', $1), DATE_TRUNC('month', $2))
- DATEDIFF(month, DATE_TRUNC('year', $1)
, DATE_TRUNC('year', CASE WHEN DATEPART(month, $1) > DATEPART(month, $2)
THEN $2 - INTERVAL '1 Year' ELSE $2 END))) > 0
THEN DATEDIFF(month, DATE_TRUNC('month', $1), DATE_TRUNC('month', $2))
- DATEDIFF(month, DATE_TRUNC('year', $1)
, DATE_TRUNC('year', CASE WHEN DATEPART(month, $1) > DATEPART(month, $2)
THEN $2 - INTERVAL '1 Year' ELSE $2 END)) || ' mons '
ELSE '' END
|| CASE WHEN ABS( DATEDIFF(day, DATE_TRUNC('day', $1)+1, DATE_TRUNC('day', $2))
- DATEDIFF(day, DATE_TRUNC('month', $1), DATE_TRUNC('month', $2))) > 0
THEN DATEDIFF(day, DATE_TRUNC('day', $1)+1, DATE_TRUNC('day', $2))
- DATEDIFF(day, DATE_TRUNC('month', $1), DATE_TRUNC('month', $2)) || ' days '
ELSE '' END
|| TO_CHAR((TIMESTAMP 'epoch'
+ ( DATEDIFF(second, $1, DATE_TRUNC('day', $1)+1 )
+ DATEDIFF(second, DATE_TRUNC('day', $2), $2) )
* INTERVAL '1 Second '),'HH24:MI:SS') age
$$ LANGUAGE SQL
;
I Found the way to get the output as same as Netezza ! And we need to create 4 Different UDF with different Inputs ! Here I've added the UDF for (TIMESTAMP, TIMESTAMP)
create or replace function AGE_UDF_V2 (START_DATE TIMESTAMP, END_DATE TIMESTAMP)
returns VARCHAR
stable
as $$
# -*- coding: utf-8 -*-
"""
Created on Wed Sep 26 12:59:24 2018
@author: pnataraj
"""
from dateutil import relativedelta
from dateutil.parser import parse
if (START_DATE is None or END_DATE is None):
return None
else:
START_DATE = str(START_DATE).strip()
END_DATE = str(END_DATE).strip()
START_DATE = parse(START_DATE)
END_DATE = parse(END_DATE)
difference = relativedelta.relativedelta(START_DATE, END_DATE)
years = difference.years
months = difference.months
days = difference.days
hours = difference.hours
minutes = difference.minutes
seconds = difference.seconds
age=''
if years != 0:
if years == 1 or years == -1:
age+=str(years)+' year '
else:
age+=str(years)+' years '
if months != 0:
if months == 1 or months == -1:
age+=str(months)+' mon '
else:
age+=str(months)+' mons '
if days != 0:
if days == 1 or days == -1:
age+=str(days)+' day '
else:
age+=str(days)+' days '
if (hours !=0 or minutes !=0 or seconds != 0):
if (hours < 0 or minutes < 0 or seconds < 0):
age+=str("-"+format(abs(hours),"02")+":"+format(abs(minutes),"02")+":"+format(abs(seconds),"02"))
else:
age+=str(format(hours,"02")+":"+format(minutes,"02")+":"+format(seconds,"02"))
elif(hours == 0 and minutes ==0 and seconds == 0):
if len(age)>0:
age = age
else:
age = "00:00:00"
return age.strip()
$$ language plpythonu;
Thanks for all the suggestions and Helps ! Hope It'll be helpful for those who're doing Nz to AWS RS Migration !
