On 11 Sep 2003, Bjørn T Johansen wrote:
> 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" <btj@havleik.no>
> >
> > > 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;
>...
Seems to work for me.
test=# select case when '22:03:21'::time - '10:34:01'::time >= 0 then '22:03:21'::time - '10:34:01'::time else
'22:03:21'::time- '10:34:01'::time + '24 hours' end;
case
----------
11:29:20
(1 row)
test=# select version();
version
---------------------------------------------------------------
PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)
There must be something different you are doing.
However, is that really the correct result? What about intervals that cross
daylight saving changes? 24 hours won't cut it in that case and you can only
tell by storing the date not just the time of day.
--
Nigel J. Andrews