--  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";