Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.3/8.9.1) with ESMTP id JAA35432 for ; Fri, 30 Apr 1999 09:04:20 -0400 (EDT) (envelope-from jose@sferacarta.com) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.6/NETTuno 3.1) with ESMTP id PAA03386; Fri, 30 Apr 1999 15:04:04 +0200 (MDT) Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 10dEXu-0000U4-00; Fri, 30 Apr 1999 14:45:42 +0000 Message-ID: <3729A5FF.F424F0CD@sferacarta.com> Date: Fri, 30 Apr 1999 14:45:51 +0200 From: =?iso-8859-1?Q?Jos=E9?= Soares X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: Mark Jewiss CC: JT Kirkpatrick , "'pgsql-sql@hub.org'" Subject: Re: [SQL] time intervals References: Content-Type: multipart/alternative; boundary="------------35A464B01DC19010AE263289" --------------35A464B01DC19010AE263289 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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' --------------35A464B01DC19010AE263289 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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'
  --------------35A464B01DC19010AE263289--