PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

标签

PostgreSQL , datediff

背景

使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。

DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} )  

周

select datediff(week,'2009-01-01','2009-12-31') as numweeks;  
  
numweeks  
----------  
52  
(1 row)  

季度

select datediff(qtr, '1998-07-01', current_date);  
  
date_diff  
-----------  
40  
(1 row)  

PostgreSQL中时间和日期可以相互加减，得到同样的结果使用extract。

PostgreSQL age, extract epoch

使用age函数对时间进行计算，得到interval。

使用extract epoch对interval 转换得到秒。

根据需求计算，转换为其他单位：日、月、季度、年、小时、秒等。

例子

postgres=# SELECT age(TO_TIMESTAMP('2016-01-01', 'YYYY-MM-DD'),TO_TIMESTAMP('2015-03-01', 'YYYY-MM-DD'));  
   age     
---------  
 10 mons  
(1 row)  
  
postgres=# SELECT EXTRACT(epoch FROM age(TO_TIMESTAMP('2016-01-01', 'YYYY-MM-DD'),TO_TIMESTAMP('2015-03-01', 'YYYY-MM-DD')));  
 date_part   
-----------  
  25920000  
(1 row)  

重新计算即可

月

日

年

小时

周

。。。 。。。

例如

天  
postgres=# SELECT EXTRACT(epoch FROM age(TO_TIMESTAMP('2016-01-01', 'YYYY-MM-DD'),TO_TIMESTAMP('2015-03-01', 'YYYY-MM-DD')))/86400;  
 ?column?   
----------  
      300  
(1 row)  
  
月  
postgres=# SELECT EXTRACT(epoch FROM age(TO_TIMESTAMP('2016-01-01', 'YYYY-MM-DD'),TO_TIMESTAMP('2015-03-01', 'YYYY-MM-DD')))/86400/30;  
 ?column?   
----------  
       10  
(1 row)  

extract支持的时间单位

https://www.postgresql.org/docs/11/static/functions-datetime.html

src/backend/utils/adt/timestamp.c

参考

https://docs.aws.amazon.com/zh_cn/redshift/latest/dg/r_DATEDIFF_function.html

https://www.postgresql.org/docs/11/static/functions-datetime.html

src/backend/utils/adt/timestamp.c

/* interval_part()  
 * Extract specified field from interval.  
 */  
Datum  
interval_part(PG_FUNCTION_ARGS)  
{  
        text       *units = PG_GETARG_TEXT_PP(0);  
        Interval   *interval = PG_GETARG_INTERVAL_P(1);  
        float8          result;  
        int                     type,  
                                val;  
        char       *lowunits;  
        fsec_t          fsec;  
        struct pg_tm tt,  
                           *tm = &tt;  
  
        lowunits = downcase_truncate_identifier(VARDATA_ANY(units),  
                                                                                        VARSIZE_ANY_EXHDR(units),  
                                                                                        false);  
  
        type = DecodeUnits(0, lowunits, &val);  
        if (type == UNKNOWN_FIELD)  
                type = DecodeSpecial(0, lowunits, &val);  
  
        if (type == UNITS)  
        {  
                if (interval2tm(*interval, tm, &fsec) == 0)  
                {  
                        switch (val)  
                        {  
                                case DTK_MICROSEC:  
                                        result = tm->tm_sec * 1000000.0 + fsec;  
                                        break;  
  
                                case DTK_MILLISEC:  
                                        result = tm->tm_sec * 1000.0 + fsec / 1000.0;  
                                        break;  
  
                                case DTK_SECOND:  
                                        result = tm->tm_sec + fsec / 1000000.0;  
                                        break;  
  
                                case DTK_MINUTE:  
                                        result = tm->tm_min;  
                                        break;  
  
                                case DTK_HOUR:  
                                        result = tm->tm_hour;  
                                        break;  
  
                                case DTK_DAY:  
                                        result = tm->tm_mday;  
                                        break;  
  
                                case DTK_MONTH:  
                                        result = tm->tm_mon;  
                                        break;  
  
                                case DTK_QUARTER:  
                                        result = (tm->tm_mon / 3) + 1;  
                                        break;  
  
                                case DTK_YEAR:  
                                        result = tm->tm_year;  
                                        break;  
  
                                case DTK_DECADE:  
                                        /* caution: C division may have negative remainder */  
                                        result = tm->tm_year / 10;  
                                        break;  
  
                                case DTK_CENTURY:  
                                        /* caution: C division may have negative remainder */  
                                        result = tm->tm_year / 100;  
                                        break;  
  
                                case DTK_MILLENNIUM:  
                                        /* caution: C division may have negative remainder */  
                                        result = tm->tm_year / 1000;  
                                        break;  
  
                                default:  
                                        ereport(ERROR,  
                                                        (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),  
                                                         errmsg("interval units \"%s\" not supported",  
                                                                        lowunits)));  
                                        result = 0;  
                        }  
  
                }  
                else  
                {  
                        elog(ERROR, "could not convert interval to tm");  
                        result = 0;  
                }  
        }  
        else if (type == RESERV && val == DTK_EPOCH)  
        {  
                result = interval->time / 1000000.0;  
                result += ((double) DAYS_PER_YEAR * SECS_PER_DAY) * (interval->month / MONTHS_PER_YEAR);  
                result += ((double) DAYS_PER_MONTH * SECS_PER_DAY) * (interval->month % MONTHS_PER_YEAR);  
                result += ((double) SECS_PER_DAY) * interval->day;  
        }  
        else  
        {  
                ereport(ERROR,  
                                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),  
                                 errmsg("interval units \"%s\" not recognized",  
                                                lowunits)));  
                result = 0;  
        }  
  
        PG_RETURN_FLOAT8(result);  
}