amazon-web-services - 是否有计算红移年、月和日日期差异的函数？

Question

在红移上，我想以年、月和日的形式显示 datediff

我尝试使用类似于 postgre 的 datediff 和 dateformat 但不起作用。

select date_format(date_diff('days','2019-01-01',current_date),'%y yr %c mth %e dy')

我预计 2 年 4 个月 26 天以上的 sql 结果

标签： amazon-web-servicesamazon-redshift

Answer 1

Postgres 有一个名为 AGE() 的函数，但 Redshift 不支持它。我为此答案使用 SQL UDF 创建了 Redshift 版本：Netezza In-Built AGE function as UDF in Redshift

/*
    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';
-- =>     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
;