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
