PostgreSQL 9.2 number of days in a tstzrange

datatypespostgresqlpostgresql-9.2

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?

Best Answer

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

Related Question