Received: from trends.net (root@clio.trends.ca [209.47.148.2]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA01214 for ; Fri, 4 Jun 1999 11:27:53 -0400 (EDT) (envelope-from jose@sferacarta.com) Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by trends.net (8.8.8/8.8.8) with ESMTP id LAA01762 for ; Fri, 4 Jun 1999 11:05:18 -0400 (EDT) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.8/NETTuno 3.2) with ESMTP id RAA21823; Fri, 4 Jun 1999 17:02:27 +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 10pxEV-00067A-00; Fri, 4 Jun 1999 16:54:16 +0000 Message-ID: <3757E80E.7A92835F@sferacarta.com> Date: Fri, 04 Jun 1999 16:51:58 +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: "Pham, Thinh" CC: "'pgsql-sql@postgresql.org'" Subject: Re: [SQL] datetime function References: <551413BBBB03D111BF880060B01AACCE0B38F2@home.priority.net> Content-Type: multipart/alternative; boundary="------------BA6A6CD65AD7C4B862619109" --------------BA6A6CD65AD7C4B862619109 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit In SQL/92: prova=> select CURRENT_TIME as now, time '18:50:00' - current_time as diff; now |diff --------+-------- 18:47:48|00:02:12 (1 row) To have this in PostgreSQL create a PL/pgSQL function like: 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 ); "Pham, Thinh" ha scritto: > Is there any sql92 functions (not really essential) that calculates the > timespan between 2 separate points in time? For example, i want to calculate > the total minutes between 2:30pm and 3:30pm. There's a function like this in > microsoft sql but i can't find it in postgres. Below is how it works in ms: > > DATEDIFF(minute, '14:30', '15:30') > > I looked at the "timespan" function but it's not the same one that i need. > Thanks, > Thinh > -- ______________________________________________________________ PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Jose' --------------BA6A6CD65AD7C4B862619109 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit In SQL/92:

prova=> select CURRENT_TIME as now, time '18:50:00' - current_time as diff;
now     |diff
--------+--------
18:47:48|00:02:12
(1 row)
 

To have this in PostgreSQL create a PL/pgSQL function like:

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

"Pham, Thinh" ha scritto:

Is there any sql92 functions (not really essential) that calculates the
timespan between 2 separate points in time? For example, i want to calculate
the total minutes between 2:30pm and 3:30pm. There's a function like this in
microsoft sql but i can't find it in postgres. Below is how it works in ms:

DATEDIFF(minute, '14:30', '15:30')

I looked at the "timespan" function but it's not the same one that i need.
Thanks,
Thinh

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