Re: [SQL] time intervals

Mark Jewiss ha scritto: <blockquote type="CITE">> I have two fields, timein & timeout, both defined as type
"time". how can <br />> i get the difference between the two??  i am connecting to postgres through <br />>
access97and trying to run a simple query to show me timeout, timein, and <br />> timeout-timein, but the calculation
isreturning an error. <p>In SQL you can do a DATEDIFF...I'm not in front of postgresql so am not <br />sure if it
supportsthis. <br /> </blockquote> DATEDIFF is not a SQL command maybe an Oracle or Sybase command. <blockquote
type="CITE"> <br />Syntax is <p>select datediff (day, mydate, 'Jan 10 1999' <br />from table <p>If mydate = Jan 9, then
theresult is one. I think datediff accepts <br />datetime, so if you specify it properly (i.e. '1 Jan 1999 10:05") then
<br/>you'll get the right answers. Just change day to whatever you need. <p>Regards, <p>Mark. <br />-- <br />Mark
Jewiss<br />Knowledge Matters Limited</blockquote><tt>You can create an operator for TIME -
TIME...</tt><tt></tt><p><tt>-- Arithmetic operations that are permitted by SQL92:</tt><br /><tt>-- 
-----------------------------------------</tt><br/><tt>--  1st operand|operator|2nd operand|result</tt><br /><tt>-- 
-----------+--------+-----------+--------</tt><br/><tt>--  datetime   |   -    |datetime   |interval</tt><br /><tt>-- 
datetime  |   +    |interval   |datetime</tt><br /><tt>--  datetime   |   -    |interval   |datetime</tt><br /><tt>-- 
interval  |   +    |datetime   |datetime</tt><br /><tt>--  interval   |   +    |interval   |interval</tt><br /><tt>-- 
interval  |   -    |interval   |interval</tt><br /><tt>--  interval   |   *    |number     |interval</tt><br /><tt>-- 
interval  |   /    |number     |interval</tt><br /><tt>--  number     |   *    |interval   |interval</tt><br /><tt>-- 
-----------+--------+-----------+--------</tt><tt></tt><p><tt>--TIME- TIME =
INTERVAL-------------------------------------------</tt><br/><tt>create function time_mi_time(time,time) returns
timespanas '</tt><br /><tt>declare</tt><br /><tt>        i1 interval;</tt><br /><tt>        i2 interval;</tt><br
/><tt>begin</tt><br/><tt>        i1:= $1;</tt><br /><tt>        i2:= $2;</tt><br /><tt>        i1:=i1 - i2;</tt><br
/><tt>       return i1;</tt><br /><tt>end;' language 'plpgsql';</tt><tt></tt><p><tt>create operator - (</tt><br
/><tt>       leftarg=time,</tt><br /><tt>        rightarg=time,</tt><br /><tt>        procedure=time_mi_time,</tt><br
/><tt>       commutator='-',</tt><br /><tt>        negator='+',</tt><br /><tt>        restrict=eqsel,</tt><br
/><tt>       join=eqjoinsel</tt><br /><tt>        );</tt><tt></tt><p><tt>hygea=> select  time '12:30' - time
'12:00';</tt><br/><tt>?column?</tt><br /><tt>--------</tt><br /><tt>00:30:00</tt><br /><tt>(1
row)</tt><tt></tt><p><tt>--</tt><br/><tt>______________________________________________________________</tt><br
/><tt>PostgreSQL6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3</tt><br
/><tt>^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^</tt><br/><tt>Jose'</tt><br /><tt></tt>