Re: [SQL] time intervals

Mark Jewiss ha scritto:

> > I have two fields, timein & timeout, both defined as type "time". how can
> > i get the difference between the two?? i am connecting to postgres through
> > access97 and trying to run a simple query to show me timeout, timein, and
> > timeout-timein, but the calculation is returning an error.
>
> In SQL you can do a DATEDIFF...I'm not in front of postgresql so am not
> sure if it supports this.
>

DATEDIFF is not a SQL command maybe an Oracle or Sybase command.

>
> Syntax is
>
> select datediff (day, mydate, 'Jan 10 1999'
> from table
>
> If mydate = Jan 9, then the result is one. I think datediff accepts
> datetime, so if you specify it properly (i.e. '1 Jan 1999 10:05") then
> you'll get the right answers. Just change day to whatever you need.
>
> Regards,
>
> Mark.
> --
> Mark Jewiss
> Knowledge Matters Limited

You can create an operator for TIME - TIME...

-- Arithmetic operations that are permitted by SQL92:
-- -----------------------------------------
-- 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
-- -----------+--------+-----------+--------

--TIME - TIME = INTERVAL-------------------------------------------
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';

create operator - (
leftarg=time,
rightarg=time,
procedure=time_mi_time,
commutator='-',
negator='+',
restrict=eqsel,
join=eqjoinsel
);

hygea=> select time '12:30' - time '12:00';
?column?
--------
00:30:00
(1 row)

--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'