pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed From: José Soares <jose@sferacarta.com>
To: Mark Jewiss <mark@office.knowledge.com>
Cc: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
Cc: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>
Subject: Re: [SQL] time intervals
Date: Fri, 30 Apr 1999 14:45:51 +0200
Message-ID: <3729A5FF.F424F0CD@sferacarta.com> (raw )
References: <Pine.BSF.4.05.9904300905520.10443-100000@office.knowledge.com >
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'
view thread (9+ messages) latest in thread
Message-ID: <3729A5FF.F424F0CD@sferacarta.com>
Permalink: ../3729A5FF.F424F0CD@sferacarta.com/
Also on: postgresql.org/message-id/3729A5FF.F424F0CD@sferacarta.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jose@sferacarta.com, mark@office.knowledge.com, jt-kirkpatrick@mpsllc.com, pgsql-sql@hub.org
Subject: Re: [SQL] time intervals
In-Reply-To: <3729A5FF.F424F0CD@sferacarta.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox