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)

Community

How to Convert a PostgreSQL Time Interval to a Numeric Value

Background

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

Database Overview

AnalyticDB for PostgreSQL

Alibaba Cloud for Manufacturing