Extract days from range type

Extract Days or Interval from a Range type value

Library Snippets

Extract days from Range type

Works with PostgreSQL

9.2+

Written in

SQL

Depends on

Nothing

To determine the the interval between the lower and upper bound of a timestamp(tz) range, or to return the number of days between the lower and lower bounds.

Functions

Two sets of overloaded functions, the first to extract the interval, the second set to extract the days. Postgresql docs about Range types: http://www.postgresql.org/docs/current/static/rangetypes.html

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;

Example

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;

 interval_with_tz | interval_no_tz | days_with_tz | days_no_tz 
------------------+----------------+--------------+------------
 1 day 19:15:00   | 1 day 19:15:00 |            3 |          3