时间戳

首页 > 解决方案 > Netezza In-Built AGE 用作 Redshift 中的 UDF

python - Netezza In-Built AGE 用作 Redshift 中的 UDF

问题描述

我正在尝试将 Redshift 中的 Netezza AGE 函数实现为 UDF。我可以在 Python（Spyder IDE - Py 3.6）中得到正确的答案，但是当我在 Redshift 中将它作为 UDF 执行时，它给了我不正确的输出。

我试图像select AGE_UDF('1994-04-04 20:10:52','2018-09-24 11:31:05');在 Redshift 中一样执行。这是 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) 中使用的代码。

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

编辑：

我找到了实现 Netezza 功能的方法，并已粘贴到此处。 我仍然期待另一种有效的方法！干杯！！！

感谢支持和建议！！！

标签： pythonamazon-web-servicesamazon-redshiftuser-defined-functionsnetezza

解决方案

不需要 Python。这是一个封装逻辑的 SQL UDF。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
;

推荐阅读