PostgreSQL interval division

Question

This has come up a couple of times, e.g., in the postgresql newsgroup and the wiki. In general, the relationship between different intervals may not be well defined - a month can be different numbers of days depending upon which month (and year) is under consideration. But sometimes it is necessary to calculate how many intervals occur between two points in time, e.g. (simplified example):

CREATE TABLE recordings(tstart timestamp, tend timestamp, interval ticklength);

SELECT (tend - tstart) / ticklength AS numticks
FROM recordings;

This isn't allowed in PostgreSQL because it is division between two intervals, which wouldn't have well defined general behaviour for the above reasons. A workaround exists for when an interval can be converted to seconds, but what is the best way when this is not the case, e.g, when the interval is of the order of milliseconds?

Answer 1

As @a_horse_with_no_name mentions in his comment, division can be achieved by conversion to milliseconds.

SELECT 
  (1000*EXTRACT(EPOCH FROM tend - tstart) 
    + EXTRACT(MILLISECOND FROM tend - tstart))
  / (1000*EXTRACT(EPOCH FROM ticklength) 
    + EXTRACT(MILLISECOND FROM ticklength)) AS numticks
FROM recordings;

Answer 2

The workaround you link to (extract epochs from the intervals and divide by them) works fine for seconds and milliseconds. So if that’s your use case, use that.

If you want to be more general and return NULL when no answer is possible, however, we have to be more complex:

-- An interval consists of a number of years, months, days, hours,
-- minutes, and seconds.  The problem of dividing two intervals lies
-- in the fact that "one month" is not a fixed number of days.
-- Therefore, dividing, say, P1M with P1D would not make sense.

CREATE OR REPLACE FUNCTION interval_months_part(INTERVAL)
        RETURNS INTEGER AS $$
        SELECT EXTRACT(years FROM $1)::INTEGER * 12
                + EXTRACT(months FROM $1)::INTEGER
$$ LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION interval_months_part(INTERVAL) IS
'Years plus months as a whole number of months';

CREATE OR REPLACE FUNCTION interval_seconds_part(INTERVAL)
        RETURNS DOUBLE PRECISION AS $$
        SELECT EXTRACT(days FROM $1) * 24 * 60 * 60
                + EXTRACT(hours FROM $1) * 60 * 60
                + EXTRACT(mins FROM $1) * 60
                + EXTRACT(secs FROM $1);
$$ LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION interval_months_part(INTERVAL) IS
'Days, hours, minutes, and seconds as seconds';

-- If we can divide exactly, do so.  Otherwise return NULL, unless
-- fudging is requested, in which case all months are implicitly
-- assumed to be 30 days, (except for 12 months, which become 365.25
-- days), by extracting and dividing the epochs.
CREATE OR REPLACE FUNCTION interval_divide(dividend INTERVAL,
                                                divisor INTERVAL,
                                                fudge BOOL DEFAULT false)
        RETURNS DOUBLE PRECISION AS $$
        SELECT CASE WHEN interval_months_part($1) = 0
                                AND interval_months_part($2) = 0
                        THEN interval_seconds_part($1)
                                / interval_seconds_part($2)
                        WHEN interval_seconds_part($1) = 0
                                AND interval_seconds_part($2) = 0
                        THEN CAST(interval_months_part($1)
                                        AS DOUBLE PRECISION)
                                / interval_months_part($2)
                        WHEN fudge
                                THEN EXTRACT(EPOCH FROM $1)
                                        / EXTRACT(EPOCH FROM $2)
                        ELSE NULL
                        END;
$$ LANGUAGE SQL IMMUTABLE STRICT;

Answer 3

While for now Postgres doesn't seem to support interval division, you can of course convert those intervals into a number you can divide - like seconds.

The other proposed answers suggest: EXTRACT(MILLISECOND FROM interval '1 day'), but I found that this sometimes returns 0. And of course one cannot divide by zero.

But the following works much nicer and is more reliable:

SELECT EXTRACT(EPOCH FROM interval '17 days') / EXTRACT(EPOCH FROM interval '1 day');

So to divide intervals - extract the number of seconds in the interval using EXTRACT(EPOCH, interval) and divide those.

Answer 4

The accepted answer is wrong.

I don't think you're supposed to add the extract milliseconds from interval as extract epoch from interval already extracts seconds with milliseconds. The documentation says:

epoch -- [...] for interval values, the total number of seconds in the interval

milliseconds -- The seconds field, including fractional parts, multiplied by 1000. Note that this includes full seconds.

You can try it out:

select extract('epoch' from '1987 years 6 months 5 days 8 hours 15 min 30.123 seconds'::interval);
date_part


62720964930.123

you see how the ".123" here tells us that the milliseconds were already included?

I think it is long overdue to add the division operator for intervals.

CREATE OR REPLACE FUNCTION interval_div(interval, interval) RETURNS float8 AS $$
  SELECT EXTRACT(EPOCH FROM $1) / EXTRACT(EPOCH FROM $2)
$$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OPERATOR / ( 
   FUNCTION = interval_div,
   LEFTARG = interval, 
   RIGHTARG = interval );

And with this:

select '1 hour'::interval / '1 second'::interval;
 ?column?
----------
     3600
select '1 hour'::interval / '1.123 second'::interval;
     ?column?

3205.699020480855

Some caveats:

select '1 month'::interval / '1 day'::interval;
 ?column?
----------
       30
integrator=> select '12 month'::interval / '1 day'::interval;
 ?column?

365.25

Julian year, but not an average Julian month.

select '1 year'::interval / '1 day'::interval / 12;
 ?column?
----------
  30.4375