Обсуждение: time intervals

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.

thanks in advance!  jt

> 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.

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

JT Kirkpatrick wrote: <blockquote type="CITE">I have two fields, timein & timeout, both defined as type "time". 
howcan <br />i get the difference between the two??  i am connecting to postgres through <br />access97 and trying to
runa simple query to show me timeout, timein, and <br />timeout-timein, but the calculation is returning an error.
<p>thanksin advance!  jt</blockquote><p><br />If your field is defined by tipe "time", the date is not important. <p>In
thiscase you can use the age(datetime, datetime) in this mode: <p><tt>a. timein <
timeout:</tt><tt></tt><p><tt>test=>select age(datetime('today'::date,'10:00:15'::time),
datetime('today'::date,'12:25:24'::time));</tt><br/><tt>age</tt><br /><tt>----------------------------</tt><br /><tt>@
2hours 25 mins 9 secs ago</tt><br /><tt>(1 row)</tt><p>or <p><tt>b. timein > timeout:</tt><br /><tt>test=> select
age(datetime('today'::date,'10:00:15'::time),datetime('today'::date,'2:25:24'::time));</tt><br /><tt>age</tt><br
/><tt>-------------------------</tt><br/><tt>@ 7 hours 34 mins 51 secs</tt><br />(1 row) <br />  <p>I use PostgreSQL
v6.5.0(09-04-1999) on Red Hat LINUX 5.2 with 2.2.5 kernel. <br />-- <br /> Best,<br />   George Moga,<br />  
george@flex.ro<br/>   Braila,ROMANIA <br />  

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>