By digoal
This function computes the interval between two timestamps and converts the result to a value in seconds or days.
How is the calculation correct?
1. Error: Time subtraction and conversion to the epoch (seconds)
When interval type is converted to epoch, the algorithm may not match the expected one:
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)
Does the epoch of 0.01 years equal 0?
Does the epoch of one year equal 365.25 days?
Does the epoch of 0.5 years equal 180 days?
Does the epoch of 0.583 years equal 180 days?
Does the epoch of 0.584 years equal 210 days?
Why?
Let's start with the make interval. The code is listed below:
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);
}
Each unit is an integer. If it is not an integer, it needs to be converted into a lower-level integer.
The integer is multiplied by this level and converted into the constant coefficient of the lower level.
Example:
Does the epoch of 0.583 years equal 180 days?
Does the epoch of 0.584 years equal 210 days?
postgres=# select 0.584*12;
?column?
----------
7.008
(1 row)
postgres=# select 0.583*12;
?column?
----------
6.996
(1 row)
After erasing the decimals, you get six months or seven months.
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)
Such an algorithm causes the result to be inconsistent with the expectation.
2. Correct: After the time is converted to epoch time, the two epoch values subtract again.
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)
digoal - July 6, 2022
digoal - July 24, 2019
digoal - May 16, 2019
digoal - December 23, 2020
Michael Peng - September 24, 2019
- January 12, 2018
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn MoreAn online MPP warehousing service based on the Greenplum Database open source program
Learn MoreGain an industrial edge with Alibaba Cloud best practices
Learn MoreAn on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn MoreMore Posts by digoal