4

How can the number of days contained within a range be found?

For example, with these timestamp ranges, get these (integer) number of days:

tstzrange('2013-10-01 07:00', '2013-10-01 07:15') | 1 (day)
tstzrange('2013-10-01 07:00', '2013-10-01 23:45') | 1 (day)
tstzrange('2013-10-01 02:00', '2013-10-02 23:45') | 2 (days)
tstzrange('2013-10-01 07:00', '2013-10-03 01:00') | 2 (days)
tstzrange('2013-10-01 01:00', '2013-10-03 23:00') | 3 (days)
tstzrange('2013-10-01 23:00', '2013-10-04 01:00') | 4 (days)

In my digging about, I've not found a built in function for this.

Is is necessary pull out the lower() and upper() timestamp elements, then get the date interval between them?

1 Answer 1

8

There are probably better ways to do this, but here are a few functions to return days or intervals from a tstzrange/tsrange input parameter.

CREATE OR REPLACE FUNCTION extract_interval(TSTZRANGE) RETURNS interval AS
$func$
select upper($1) - lower($1);
$func$ LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION extract_interval(TSRANGE) RETURNS interval AS
$func$
select upper($1) - lower($1);
$func$ LANGUAGE sql STABLE;


CREATE OR REPLACE FUNCTION extract_days(TSTZRANGE) RETURNS integer AS
$func$
select (date_trunc('day',upper($1))::DATE - date_trunc('day',lower($1))::DATE) + 1;
$func$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION extract_days(TSRANGE) RETURNS integer AS
$func$
select (date_trunc('day',upper($1))::DATE - date_trunc('day',lower($1))::DATE) + 1;
$func$ LANGUAGE sql;


WITH tzr AS (SELECT tstzrange('2013-10-01 10:00-07', '2013-10-03 05:15-07') AS dttz, tstzrange('2013-10-01 10:00', '2013-10-03 05:15') AS dt)
SELECT  extract_interval(dttz) as interval_with_tz,
        extract_interval(dt) as interval_no_tz,
        extract_days(dttz) as days_with_tz,
        extract_days(dt) as days_no_tz
FROM tzr;

                      tstzrange                      | interval_with_tz | interval_no_tz | days_with_tz | days_no_tz 
-----------------------------------------------------+------------------+----------------+--------------+------------
 ["2013-10-01 07:00:00-07","2013-10-01 07:15:00-07") | 00:15:00         | 00:15:00       |            1 |          1

Edit: I have put up these functions on the Postgresql Wiki so that others can use or edit them if more efficient versions are developed. https://wiki.postgresql.org/wiki/Extract_days_from_range_type

1
  • NOTE: Those functions don't exactly return the correct data in the case where the bounds are exclusive. In the given example, 07:15 is NOT actually in the given range.
    – Jim Nasby
    Commented Jul 25, 2016 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.