How to Convert a PostgreSQL Time Interval to a Numeric Value
By digoal
Background
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);
}
- MONTHS_PER_YEAR
- USECS_PER_SEC
- SECS_PER_HOUR
- SECS_PER_MINUTE
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)
Read previous post:
Read next post:
Comments
Related Products
-
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn More -
AnalyticDB for PostgreSQL
An online MPP warehousing service based on the Greenplum Database open source program
Learn More -
Alibaba Cloud for Manufacturing
Gain an industrial edge with Alibaba Cloud best practices
Learn More -
ApsaraDB RDS for PostgreSQL
An on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn More
More Posts by digoal
- Tips for Executing SQL Statements in Batch: Which Is Better, gexec or Dynamic SQL?
- What Could Be the Factor Leading to the Slow Execution of the First SQL Statement?
- How to Remove Duplicate and Similar Contents in Vector Databases
- How to Implement Parallelism and Concurrency Control (Queue) in Shell
- How to Determine Whether the Current PostgreSQL Database is in a Consistent State?
- PolarDB for PostgreSQL | Retrieval and Matching-Degree Sorting by Tags on Blogs and Websites
- Three Key Details and Principles of PostgreSQL Online Backup & Recovery
- PolarDB for PostgreSQL | Database Performance Optimization for High-Concurrency Queue Processing
- Implementing PostgreSQL Hook: Stats on Tables with Full Scans & Corresponding SQLs
- Optimization on Memory Usage During Rust Cargo Code Compiling