hdate.sql

application/octet-stream

Download

DROP TYPE IF EXISTS hdate CASCADE;

--- Data type

CREATE TYPE hdate AS (
       start_time TIMESTAMP,
       end_time TIMESTAMP
);

--- Constructors

CREATE OR REPLACE FUNCTION hdate(timestamp)
RETURNS hdate AS
$$
SELECT ($1, $1)::hdate;
$$
LANGUAGE 'sql' IMMUTABLE STRICT
COST 1;

CREATE OR REPLACE FUNCTION hdate(timestamp, timestamp)
RETURNS hdate AS
$$
SELECT (LEAST($1,$2), GREATEST($1,$2))::hdate;
$$
LANGUAGE 'sql' IMMUTABLE STRICT
COST 1;

CREATE OR REPLACE FUNCTION hdate(timestamp, interval) 
RETURNS hdate AS
$$
SELECT CASE WHEN $2 >= interval '0 seconds' THEN
($1, $1 + $2)::hdate
WHEN $2 < interval '0 seconds' THEN
hdate($1 + $2, $1)::hdate END;
$$
LANGUAGE 'sql' IMMUTABLE STRICT
COST 1;

CREATE OR REPLACE FUNCTION hdate(str1 TEXT, str2 TEXT)
RETURNS hdate AS 
$$
DECLARE
t1 timestamp;
t2 timestamp;
i2 interval;
hd hdate;
BEGIN
t1 := str1::timestamp;
BEGIN
 t2 := str2::timestamp;
 hd := hdate(t1, t2);
 EXCEPTION
  WHEN invalid_datetime_format THEN 
  BEGIN
   i2 := str2::interval;
   hd := hdate(t1, i2);
  EXCEPTION
   WHEN invalid_datetime_format THEN
    hd := hdate(t1);
  END;
 END;
RETURN hd;
END;
$$ 
LANGUAGE 'plpgsql' IMMUTABLE
COST 1;

CREATE OR REPLACE FUNCTION hdate(str TEXT)
RETURNS hdate AS 
$$
DECLARE
t timestamp;
i interval;
a text[];
hd hdate;
BEGIN

--- year only
a := regexp_matches(str, E'^(\\d\\d\\d\\d)$');
IF a IS NOT NULL THEN 
t := (a[1] || '.01.01')::timestamp;
i := '1 year -1 second'::interval;
END IF;

--- year + month
a := regexp_matches(str, E'^(\\d\\d\\d\\d)[./:-]((?:\\d){1,2})$');
IF a IS NOT NULL THEN 
t := (a[1] || '.' || a[2] || '.01')::timestamp;
i := '1 month -1 second'::interval;
END IF;

--- year + month + day
a := regexp_matches(str, E'^(\\d\\d\\d\\d)[./:-]((?:\\d){1,2})[./:-]((?:\\d){1,2})$');
IF a IS NOT NULL THEN 
t := (a[1] || '.' || a[2] || '.' || a[3])::timestamp;
i := '1 day -1 second'::interval;
END IF;

--- fallback to builtin parser
IF t IS NULL THEN
t := str::timestamp;
hd := hdate(t);
ELSE
hd := hdate(t, i);
END IF;

RETURN hd;
END;
$$ 
LANGUAGE 'plpgsql' IMMUTABLE
COST 1;

--- Functions

CREATE OR REPLACE FUNCTION hdate_before(hdate, hdate)
RETURNS boolean AS
$$
SELECT ($1).end_time <= ($2).start_time;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION hdate_before_fuzzy(hdate, hdate)
RETURNS boolean AS
$$
SELECT ($1).end_time <= ($2).end_time AND ($1).start_time <= ($2).start_time;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION hdate_after(hdate, hdate)
RETURNS boolean AS
$$
SELECT ($1).start_time >= ($2).end_time;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION hdate_after_fuzzy(hdate, hdate)
RETURNS boolean AS
$$
SELECT ($1).start_time >= ($2).start_time AND ($1).end_time >= ($2).end_time;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION hdate_contained(hdate, hdate)
RETURNS boolean AS
$$
SELECT ($1).start_time >= ($2).start_time AND ($1).end_time <= ($2).end_time;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION hdate_contains(hdate, hdate)
RETURNS boolean AS
$$
SELECT ($1).start_time <= ($2).start_time AND ($1).end_time >= ($2).end_time;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION hdate_contains_or_contained(hdate, hdate)
RETURNS boolean AS
$$
SELECT (($1).start_time <= ($2).start_time AND ($1).end_time >= ($2).end_time) 
OR (($1).start_time >= ($2).start_time AND ($1).end_time <= ($2).end_time);
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION hdate_intersect(hdate, hdate)
RETURNS boolean AS
$$
SELECT ($1).start_time <= ($2).end_time AND ($1).end_time >= ($2).start_time;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION hdate_interval(hdate)
RETURNS interval AS
$$
SELECT ($1).end_time - ($1).start_time;
$$
LANGUAGE 'sql' IMMUTABLE;

--- Operators

CREATE OPERATOR > (
LEFTARG = hdate,
RIGHTARG = hdate,
PROCEDURE = hdate_after
);

CREATE OPERATOR >= (
LEFTARG = hdate,
RIGHTARG = hdate,
PROCEDURE = hdate_after_fuzzy
);

CREATE OPERATOR < (
LEFTARG = hdate,
RIGHTARG = hdate,
PROCEDURE = hdate_before
);

CREATE OPERATOR <= (
LEFTARG = hdate,
RIGHTARG = hdate,
PROCEDURE = hdate_before_fuzzy
);

CREATE OPERATOR = (
LEFTARG = hdate,
RIGHTARG = hdate,
PROCEDURE = hdate_contains_or_contained
);

CREATE OPERATOR @> (
LEFTARG = hdate,
RIGHTARG = hdate,
PROCEDURE = hdate_contains
);

CREATE OPERATOR <@ (
LEFTARG = hdate,
RIGHTARG = hdate,
PROCEDURE = hdate_contained
);

CREATE OPERATOR && (
LEFTARG = hdate,
RIGHTARG = hdate,
PROCEDURE = hdate_intersect
);

---