Extract days from range type

From PostgreSQL wiki

Revision as of 18:11, 27 October 2013 by Brick (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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
Personal tools