This is too weird... and you are sure you haven't modified anything in pg_operator,
used CREATE OPERATOR or something like that??
I think it's time for you to send in a bug report......
/Mattias
----- Original Message -----
From: "Bjørn T Johansen" <[EMAIL PROTECTED]>
> Well, here is my output..:
>
> DT=# create table mytable (starttime time, stoptime time);
> CREATE TABLE
> DT=# insert into mytable values ('10:45', '22:30');
> INSERT 20746 1
> DT=# insert into mytable values ('19:45', '04:30');
> INSERT 20747 1
> DT=# insert into mytable values ('00:00', '00:00');
> INSERT 20748 1
> DT=# insert into mytable values ('23:59', '00:01');
> INSERT 20749 1
> DT=# insert into mytable values ('00:01', '23:59');
> INSERT 20750 1
> DT=# select starttime,stoptime,(case when stoptime-starttime >= 0 then
> stoptime-starttime else stoptime-starttime+'24 hours' end) as timediff
> from mytable;
> starttime | stoptime | timediff
> -----------+----------+----------
> 10:45:00 | 22:30:00 | 11:45
> 19:45:00 | 04:30:00 | -15:15
> 00:00:00 | 00:00:00 | 00:00
> 23:59:00 | 00:01:00 | -23:58
> 00:01:00 | 23:59:00 | 23:58
> (5 rows)
>
> DT=#
>
> Strange....
>
> On Thu, 2003-09-11 at 15:25, Mattias Kregert wrote:
> > Very strange indeed!
> >
> > This is my output.
> > ------------------------------------
> >
> > Welcome to psql 7.3.3, the PostgreSQL interactive terminal.
> >
> >
> > Type: \copyright for distribution terms
> > \h for help with SQL commands
> > \? for help on internal slash commands
> > \g or terminate with semicolon to execute query
> > \q to quit
> >
> >
> > test=# create table mytable (starttime time, stoptime time);
> > CREATE TABLE
> > test=# insert into mytable values ('10:45', '22:30');
> > INSERT 103688 1
> > test=# insert into mytable values ('19:45', '04:30');
> > INSERT 103689 1
> > test=# insert into mytable values ('00:00', '00:00');
> > INSERT 103690 1
> > test=# insert into mytable values ('23:59', '00:01');
> > INSERT 103691 1
> > test=# insert into mytable values ('00:01', '23:59');
> > INSERT 103692 1
> > test=# select starttime,stoptime,(case when stoptime-starttime >= 0 then
> > stoptime-starttime else stoptime-starttime+'24 hours' end) as timediff from
> > mytable;
> > starttime | stoptime | timediff
> > -----------+----------+----------
> > 10:45:00 | 22:30:00 | 11:45
> > 19:45:00 | 04:30:00 | 08:45
> > 00:00:00 | 00:00:00 | 00:00
> > 23:59:00 | 00:01:00 | 00:02
> > 00:01:00 | 23:59:00 | 23:58
> > (5 rows)
> >
> >
> > test=#
> > ------------------------------------
> >
> > As you see, it all works as it should. Can you do exactly the same and send me the
> > complete output? If you get a different result, then it's time to send in a bug
> > report...
> >
> > /Mattias
> >
> >
> > ----- Original Message -----
> > From: "Bjørn T Johansen" <[EMAIL PROTECTED]>
> > > Yes, I am sure, I just use copy-and-paste and I have double checked....
> > > I am running on 7.3.4 but that shouldn't make any difference?
> > >
> > > BTJ
> > >
> > > On Thu, 2003-09-11 at 14:56, Mattias Kregert wrote:
> > > > When i run it, it works as intended (on pg 7.3.3). Which version do you use?
> > > >
> > > > Are you absolutely sure you copied it exactly? You typed in '>=' and not '=',
> > > > right?
> > > >
> > > > /Mattias
> > > >
> > > > ----- Original Message -----
> > > > From: "Bjørn T Johansen" <[EMAIL PROTECTED]>
> > > > To: "Mattias Kregert" <[EMAIL PROTECTED]>
> > > > Cc: <[EMAIL PROTECTED]>
> > > > Sent: Thursday, September 11, 2003 3:02 PM
> > > > Subject: Re: [GENERAL] I need a SQL...
> > > >
> > > >
> > > > > Well, it's close... :)
> > > > >
> > > > > But it looks like the case doesn't work..
> > > > > If I run your sql, the timediff is negative.
> > > > >
> > > > > But if I run this:
> > > > > SELECT (stoptime-starttime+'24 hours') as timediff FROM mytable
> > > > > the timediff has correct value..
> > > > >
> > > > > Do you see any error in the case, cause I don't?
> > > > >
> > > > >
> > > > > BTJ
> > > > >
> > > > > On Thu, 2003-09-11 at 14:29, Mattias Kregert wrote:
> > > > > > Solution:
> > > > > >
> > > > > > SELECT starttime, stoptime, (CASE WHEN stoptime-starttime >= 0 THEN
> > > > > > stoptime-starttime ELSE stoptime-starttime+'24 hours' END) as timediff
> > > > > > FROM mytable;
> > > > > >
> > > > > > /Mattias
> > > > > >
> > > > > > ----- Original Message -----
> > > > > > From: Bjørn T Johansen
> > > > > > To: Andrew L. Gould
> > > > > > Cc: PostgreSQL general list
> > > > > > Sent: Thursday, September 11, 2003 2:12 PM
> > > > > > Subject: Re: [GENERAL] I need a SQL...
> > > > > >
> > > > > > On Thu, 2003-09-11 at 14:07, Andrew L. Gould wrote:
> > > > > > > On Thursday 11 September 2003 06:25 am, Bjørn T Johansen wrote:
> > > > > > > > I need to write a SQL that calculates the interval between a
> > > > > > start time
> > > > > > > > and a stop time. This is the easy part. The problem is that I
> > > > > > only have
> > > > > > > > the time part, i.e. no date, so how can I be sure to also
> > > > > > calculate the
> > > > > > > > interval if the start time is before midnight and the stop
> > > > > > time is after
> > > > > > > > midnight?
> > > > > > > >
> > > > > > > >
> > > > > > > > Regards,
> > > > > > > >
> > > > > > > > BTJ
> > > > > > >
> > > > > > > If the activity or period you are measuring can equal or exceed
> > > > > > 12 hours, you
> > > > > > > won't be able to calculate it reliably without a start date and
> > > > > > a stop date.
> > > > > > > If the periods are always less than 12 hours (and you assume all
> > > > > > the data is
> > > > > > > good), then stop times that are less than start times would
> > > > > > indicate an
> > > > > > > intervening midnight.
> > > > > > >
> > > > > > > The dates do not have to be in the same fields as the times,
> > > > > > since you can add
> > > > > > > date and time data to create a timestamp for datetime
> > > > > > calculations:
> > > > > > >
> > > > > > > (stop_date + stop_time) - (start_date + start_time)
> > > > > > >
> > > > > > > Best of luck,
> > > > > > >
> > > > > > > Andrew Gould
> > > > > >
> > > > > >
> > > > > >
> > > > > > Yes, the period can exceed 12 hours, so what you are saying is
> > > > > > that this is not possible to solve
> > > > > > without the date part? I can write this logic in my business logic
> > > > > > but I was hoping to
> > > > > > solve this in my database layer...
> > > > > >
> > > > > >
> > > > > > BTJ
> > > > >
> > > > >
> > > > > ---------------------------(end of broadcast)---------------------------
> > > > > TIP 4: Don't 'kill -9' the postmaster
> > > > >
> > > --
> > > -----------------------------------------------------------------------------------------------
> > > Bjørn T Johansen (BSc,MNIF)
> > > Executive Manager
> > > [EMAIL PROTECTED] Havleik Consulting
> > > Phone : +47 67 54 15 17 Conradisvei 4
> > > Fax : +47 67 54 13 91 N-1338 Sandvika
> > > Cellular : +47 926 93 298 http://www.havleik.no
> > > -----------------------------------------------------------------------------------------------
> > > "The stickers on the side of the box said "Supported Platforms: Windows
> > > 98, Windows NT 4.0,
> > > Windows 2000 or better", so clearly Linux was a supported platform."
> > > -----------------------------------------------------------------------------------------------
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 7: don't forget to increase your free space map settings
> > >
>
>
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
