interval.sql
text/plain
Filename: interval.sql
Type: text/plain
Part: 0
-- These functions create SQL92 operators between datetime and interval
-- and vice versa:
-- -----------------------------------------
-- 1st operand|operator|2nd operand|result
-- -----------+--------+-----------+--------
-- datetime | - |datetime |interval
-- datetime | + |interval |datetime
-- datetime | - |interval |datetime
-- interval | + |datetime |datetime
-- interval | + |interval |interval
-- interval | - |interval |interval
-- interval | * |number |interval
-- interval | / |number |interval
-- number | * |interval |interval
-- -----------+--------+-----------+--------
set datestyle to 'iso';
--DATE + INTERVAL = DATE----------------------------------------------------
drop function date_pl_interval(date,timespan);
create function date_pl_interval(date,timespan) returns date as
'
begin
return date(datetime_pl_span($1,$2));
end; ' language 'plpgsql';
drop operator + (date,timespan);
create operator + (
leftarg=date,
rightarg=timespan,
procedure=date_pl_interval,
commutator='+',
negator='-',
restrict=eqsel,
join=eqjoinsel
);
--DATE - INTERVAL = DATE----------------------------------------------------
drop function date_mi_interval(date,timespan);
create function date_mi_interval(date,timespan) returns date as
'
begin
return date(datetime_mi_span($1,$2));
end; ' language 'plpgsql';
drop operator - (date,timespan);
create operator - (
leftarg=date,
rightarg=timespan,
procedure=date_mi_interval,
commutator='-',
negator='+',
restrict=eqsel,
join=eqjoinsel
);
--INTERVAL + DATE = DATE----------------------------------------------------
drop function interval_pl_date(timespan,date);
create function interval_pl_date(timespan,date) returns date as
'
begin
return date(datetime_pl_span($2,$1));
end; ' language 'plpgsql';
drop operator + (timespan,date);
create operator + (
leftarg=timespan,
rightarg=date,
procedure=interval_pl_date,
commutator='+',
negator='-',
restrict=eqsel,
join=eqjoinsel
);
--INTERVAL + TIMESTAMP = TIMESTAMP-----------------------------------------------
drop function interval_pl_timestamp(timespan,timestamp);
create function interval_pl_timestamp(timespan,timestamp) returns timestamp as
'
begin
return datetime_pl_span($2,$1);
end; ' language 'plpgsql';
drop operator + (timespan,timestamp);
create operator + (
leftarg=timespan,
rightarg=timestamp,
procedure=interval_pl_timestamp,
commutator='+',
negator='-',
restrict=eqsel,
join=eqjoinsel
);
--INTERVAL / NUMBER = INTERVAL------------------------------------------------
drop function interval_div_int4(timespan,integer);
create function interval_div_int4(timespan,integer) returns timespan as
'
begin
return timespan_div($1,float8($2));
end; ' language 'plpgsql';
drop operator / (timespan,int4);
create operator / (
leftarg=timespan,
rightarg=int4,
procedure=interval_div_int4,
commutator='/',
negator='*',
restrict=eqsel,
join=eqjoinsel
);
drop operator / (timespan,float8);
create operator / (
leftarg=timespan,
rightarg=float8,
procedure=timespan_div,
commutator='/',
negator='*',
restrict=eqsel,
join=eqjoinsel
);
--NUMBER * INTERVAL = INTERVAL------------------------------------------------
drop function float8_mul_interval(float8,timespan);
create function float8_mul_interval(float8,timespan) returns timespan as
'declare
i float8;
begin
i:= date_part(''epoch'',$2);
return i * $1;
end;
' language 'plpgsql';
drop function int4_mul_interval(integer,timespan);
create function int4_mul_interval(integer,timespan) returns timespan as
'
begin
return float8_mul_interval(float8($1),$2);
end;
' language 'plpgsql';
drop operator * (float8,timespan);
create operator * (
leftarg=float8,
rightarg=timespan,
procedure=float8_mul_interval,
commutator='*',
negator='/',
restrict=eqsel,
join=eqjoinsel
);
drop operator * (int4,timespan);
create operator * (
leftarg=int4,
rightarg=timespan,
procedure=int4_mul_interval,
commutator='*',
negator='/',
restrict=eqsel,
join=eqjoinsel
);
--INTERVAL * NUMBER = INTERVAL------------------------------------------------
drop function interval_mul_float8(timespan,float8);
create function interval_mul_float8(timespan,float8) returns timespan as
'declare
i float8;
begin
i:= date_part(''epoch'',$1);
return i * $2;
end;
' language 'plpgsql';
drop function interval_mul_int4(timespan,integer);
create function interval_mul_int4(timespan,integer) returns timespan as
'
begin
return interval_mul_float8($1,float8($2));
end;
' language 'plpgsql';
drop operator * (timespan,int4);
create operator * (
leftarg=timespan,
rightarg=int4,
procedure=interval_mul_int4,
commutator='*',
negator='/',
restrict=eqsel,
join=eqjoinsel
);
drop operator * (timespan,float8);
create operator * (
leftarg=timespan,
rightarg=float8,
procedure=interval_mul_float8,
commutator='*',
negator='/',
restrict=eqsel,
join=eqjoinsel
);
--TIME + INTERVAL = TIME-----------------------------------time??
drop function time_pl_interval(time,timespan);
create function time_pl_interval(time,timespan) returns time as
'
declare
i1 timespan;
i2 timespan;
begin
i1:= $1;
i2:= $2;
i1:=i1 + i2;
return i1;
end;
' language 'plpgsql';
drop operator + (time,timespan);
create operator + (
leftarg=time,
rightarg=timespan,
procedure=time_pl_interval,
commutator='+',
negator='-',
restrict=eqsel,
join=eqjoinsel
);
--INTERVAL + TIME = TIME------------------------------------------------
drop function interval_pl_time(timespan,time);
create function interval_pl_time(timespan,time) returns time as
'
begin
return $2 + $1;
end;
' language 'plpgsql';
drop operator + (timespan,time);
create operator + (
leftarg=timespan,
rightarg=time,
procedure=interval_pl_time,
commutator='+',
negator='-',
restrict=eqsel,
join=eqjoinsel
);
--TIME - TIME = INTERVAL-------------------------------------------
drop function time_mi_time(time,time);
create function time_mi_time(time,time) returns timespan as
'
declare
i1 interval;
i2 interval;
begin
i1:= $1;
i2:= $2;
i1:=i1 - i2;
return i1;
end;
' language 'plpgsql';
drop operator - (time,time);
create operator - (
leftarg=time,
rightarg=time,
procedure=time_mi_time,
commutator='-',
negator='+',
restrict=eqsel,
join=eqjoinsel
);
--TIME - INTERVAL = TIME------------------------------------------------
drop function time_mi_interval(time,timespan);
create function time_mi_interval(time,timespan) returns time as
'
declare
i2 time;
begin
i2:= $2;
return $1 - i2;
end;
' language 'plpgsql';
drop operator - (time,timespan);
create operator - (
leftarg=time,
rightarg=timespan,
procedure=time_mi_interval,
commutator='-',
negator='+',
restrict=eqsel,
join=eqjoinsel
);
--TIMESTAMP + INTERVAL = TIMESTAMP-------------------------------------------
drop function timestamp_pl_interval(timestamp,timespan);
create function timestamp_pl_interval(timestamp,timespan) returns timestamp as
'
begin
return datetime_pl_span($1,$2);
end;
' language 'plpgsql';
drop operator + (timestamp,timespan);
create operator + (
leftarg=timestamp,
rightarg=timespan,
procedure=timestamp_pl_interval,
commutator='+',
negator='-',
restrict=eqsel,
join=eqjoinsel
);
--TIMESTAMP - INTERVAL = TIMESTAMP----------------------------------------------
drop function timestamp_mi_interval(timestamp,timespan);
create function timestamp_mi_interval(timestamp,timespan) returns timestamp as
'
begin
return datetime_mi_span($1,$2);
end; ' language 'plpgsql';
drop operator - (timestamp,timespan);
create operator - (
leftarg=timestamp,
rightarg=timespan,
procedure=timestamp_mi_interval,
commutator='-',
negator='+',
restrict=eqsel,
join=eqjoinsel
);
--TIMESTAMP - TIMESTAMP = INTERVAL-----------------------------------------------
drop function timestamp_mi_timestamp(timestamp,timestamp);
create function timestamp_mi_timestamp(timestamp,timestamp) returns timespan as
'
declare
i1 timespan;
i2 timespan;
begin
i1:= datetime_part(''epoch'',$1);
i2:= datetime_part(''epoch'',$2);
return i1 - i2;
end;
' language 'plpgsql';
drop operator - (timestamp,timestamp);
create operator - (
leftarg=timestamp,
rightarg=timestamp,
procedure=timestamp_mi_timestamp,
commutator='-',
negator='+',
restrict=eqsel,
join=eqjoinsel
);
--EXAMPLES--------------------------------------------------------------------
--datetimes + interval
SELECT DATE '1999-02-11' + INTERVAL '1 MONTH' as "date 1999-03-11";
SELECT TIME '15:00:00' + INTERVAL '1 HOUR' as "16:00:00 time";
SELECT TIMESTAMP '1999-02-11 15:00:00' + INTERVAL '1 MONTH' as "1999-03-11 15:00:00+01 timestamp";
--datetimes - datetime
SELECT DATE '1999-02-11' - DATE '1999-02-01' as "interval 10";
SELECT TIME '15:00:00'- TIME '01:30:00' as "13:30 interval";
SELECT TIMESTAMP '1999-02-11 15:00:00'- TIMESTAMP '1999-02-01 01:30:00' as "10 13:30 interval";
--datetimes - interval
SELECT DATE '1999-02-11' - INTERVAL '1 MONTH' as "date 1999-01-11";
SELECT TIME '15:00:00'- INTERVAL '1 HOUR' as "14:00:00 time";
SELECT TIMESTAMP '1999-02-11 15:00:00'- INTERVAL '1 MONTH' as "1999-01-11 15:00:00+01 timestamp";
--interval + datetime
SELECT INTERVAL '1 DAY' + DATE '1999-02-11' as "date 1999-02-12";
SELECT INTERVAL '3 hour' + TIME '15:00:00' as "18:00:00 time";
SELECT INTERVAL '3 HOUR' + TIMESTAMP '1999-02-11 15:00:00' as "1999-02-11 18:00:00+01 timestamp";
--interval + interval
SELECT INTERVAL '3 hour' + INTERVAL '1 hour' as "04:00 interval";
--interval - interval
SELECT INTERVAL '4 hour' - INTERVAL '3 hour' as "01:00 interval";
--interval / number
SELECT INTERVAL '4 hour' / 2.0 as "02:00 interval";
--interval * number
SELECT INTERVAL '4 hour' * 3 as "12:00 interval";
--number * interval
SELECT 3 * INTERVAL '4 hour' as "12:00 interval";