作者

digoal

日期

2020-08-12

标签

PostgreSQL , 计算时间间隔 , 数值

背景

计算两个时间戳的间隔, 然后转化为秒或者转化为天为单位的数值.

怎么算才是正确的?

1、错误: 时间相减, 然后转化为epoch (秒数)

因为interval类型转换为epoch时, 算法可能和预期不符.

``` 
postgres=# select extract('epoch' from interval '0.01 year')/3600/24.0; 
?column?

    0

(1 row)

postgres=# select extract('epoch' from interval '1 year')/3600/24.0; 
?column?

365.25 
(1 row)

postgres=# select extract('epoch' from interval '0.5 year')/3600/24.0; 
?column?

  180

(1 row)

postgres=# select extract('epoch' from interval '0.583 year')/3600/24.0; 
?column?

  180

(1 row)

postgres=# select extract('epoch' from interval '0.584 year')/3600/24.0; 
?column?

  210

(1 row) 
```

0.01年的epoch是0 ?

1年的epoch是365.25天?

0.5年的epoch是180天?

0.583年的epoch是180天?

0.584年的epoch是210天?

为什么?

原因要从make interval说起, 代码如下:

src/backend/utils/adt/timestamp.c

``` 
/ 
* make_interval - numeric Interval constructor 
/ 
Datum 
make_interval(PG_FUNCTION_ARGS) 
{ 
int32 years = PG_GETARG_INT32(0); 
int32 months = PG_GETARG_INT32(1); 
int32 weeks = PG_GETARG_INT32(2); 
int32 days = PG_GETARG_INT32(3); 
int32 hours = PG_GETARG_INT32(4); 
int32 mins = PG_GETARG_INT32(5); 
double secs = PG_GETARG_FLOAT8(6); 
Interval *result;

    /*    
     * Reject out-of-range inputs.  We really ought to check the integer    
     * inputs as well, but it's not entirely clear what limits to apply.    
     */    
    if (isinf(secs) || isnan(secs))    
            ereport(ERROR,    
                            (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),    
                             errmsg("interval out of range")));

    result = (Interval *) palloc(sizeof(Interval));    
    result->month = years * MONTHS_PER_YEAR + months;    
    result->day = weeks * 7 + days;

    secs = rint(secs * USECS_PER_SEC);    
    result->time = hours * ((int64) SECS_PER_HOUR * USECS_PER_SEC) +    
            mins * ((int64) SECS_PER_MINUTE * USECS_PER_SEC) +    
            (int64) secs;

    PG_RETURN_INTERVAL_P(result);

} 
```

MONTHS_PER_YEAR 
USECS_PER_SEC 
SECS_PER_HOUR 
SECS_PER_MINUTE

每个单位都是整数, 如果不是整数, 则需要转换为下一级的整数

整数再乘以这个级别转换为下一级别的常数系数

例如

0.583年的epoch是180天? 
0.584年的epoch是210天?

``` 
postgres=# select 0.584*12; 
?column?

7.008

(1 row)

postgres=# select 0.583*12; 
?column?

6.996

(1 row) 
```

抹掉小数后得到6个月,7个月.

``` 
postgres=# select interval '0.583 year'; 
interval

6 mons 
(1 row)

postgres=# select interval '0.584 year'; 
interval

7 mons 
(1 row)

postgres=# select interval '0.11 month'; 
interval

3 days 07:12:00 
(1 row) 
```

这样的算法, 造成结果与预期不符.

2、正确: 时间转化为epoch后, 两个epoch值再相减.

``` 
postgres=# select extract('epoch' from now()) - extract('epoch' from timestamp '2018-10-01'); 
?column?

58863397.59471512 
(1 row)

postgres=# select (extract('epoch' from now()) - extract('epoch' from timestamp '2018-10-01'))/3600.0/24.0; 
?column?

681.289469844514 
(1 row) 
```

参考自：https://cdn.modb.pro/db/91966