Netezza内置AGE功能在Redshift中用作UDF [英] Netezza In-Built AGE function as UDF in Redshift
问题描述
我试图在Redshift中将Netezza AGE功能实现为UDF.我可以在Python(Spyder IDE-Py 3.6)中获得正确的答案,但是当我在Redshift中将其作为UDF执行时,它给了我错误的输出.
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.
我试图在Redshift中以select AGE_UDF('1994-04-04 20:10:52','2018-09-24 11:31:05');
的身份执行.
这是RS UDF中使用的代码.
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;
RS中的输出: -8809.15:20:13
这是Python(3.6)中使用的代码.
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)
Python输出: 24年5个月19天15:20:13
Output in Python: 24 years 5 mons 19 days 15:20:13
我找到了实现Netezza功能的方法,并将其粘贴在这里. 我仍然期待另一种有效的方法!干杯!!!
I found the way to achieve the Netezza functionality and I've pasted here. Still I'm Expecting an another efficient way !!! Cheers !!!
感谢您的支持和建议!!!
推荐答案
我找到了与Netezza一样获得输出的方法!我们需要用不同的输入创建4个不同的UDF!在这里,我为(TIMESTAMP,TIMESTAMP)添加了UDF
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;
感谢所有建议和帮助!希望对正在执行Nz到AWS RS迁移的人有帮助!
Thanks for all the suggestions and Helps ! Hope It'll be helpful for those who're doing Nz to AWS RS Migration !
这篇关于Netezza内置AGE功能在Redshift中用作UDF的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!