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

 · 

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