标签
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);
} 
