Thread: I need a SQL...

I need a SQL...

From
Bjørn T Johansen
Date:
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

-- 
-----------------------------------------------------------------------------------------------
Bjørn T Johansen (BSc,MNIF)
Executive Manager
btj@havleik.no                  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."
-----------------------------------------------------------------------------------------------

Re: I need a SQL...

From
"Andrew L. Gould"
Date:
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

Re: I need a SQL...

From
Bjørn T Johansen
Date:
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

Re: I need a SQL...

From
"Mattias Kregert"
Date:
 
 
dsfdfd
----- Original Message -----
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:
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
dsfdf

Re: I need a SQL...

From
"Mattias Kregert"
Date:
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 -----
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

Re: I need a SQL...

From
Bjørn T Johansen
Date:
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


Re: I need a SQL...

From
"Mattias Kregert"
Date:
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>
To: "Mattias Kregert" <mattias@kregert.se>
Cc: <pgsql-general@postgresql.org>
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
>

Re: I need a SQL...

From
Bjørn T Johansen
Date:
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>
> To: "Mattias Kregert" <mattias@kregert.se>
> Cc: <pgsql-general@postgresql.org>
> 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
btj@havleik.no                  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."
-----------------------------------------------------------------------------------------------



Re: I need a SQL...

From
"Mattias Kregert"
Date:
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
differentresult, then it's time to send in a bug report... 

/Mattias


----- Original Message -----
From: "Bjørn T Johansen" <btj@havleik.no>
> 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>
> > To: "Mattias Kregert" <mattias@kregert.se>
> > Cc: <pgsql-general@postgresql.org>
> > 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
> btj@havleik.no                  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
>

Re: I need a SQL...

From
"Nigel J. Andrews"
Date:

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


Re: I need a SQL...

From
Bjørn T Johansen
Date:
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+'24hours' 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
differentresult, then it's time to send in a bug report... 
>
> /Mattias
>
>
> ----- Original Message -----
> From: "Bjørn T Johansen" <btj@havleik.no>
> > 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>
> > > To: "Mattias Kregert" <mattias@kregert.se>
> > > Cc: <pgsql-general@postgresql.org>
> > > 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
> > btj@havleik.no                  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
> >



Re: I need a SQL...

From
"Mattias Kregert"
Date:
This is too weird... and you are sure you haven't modified anything in pg_operator, used CREATE OPERATOR or something
likethat?? 

I think it's time for you to send in a bug report......

/Mattias

----- Original Message -----
From: "Bjørn T Johansen" <btj@havleik.no>
> 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+'24hours' 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
differentresult, then it's time to send in a bug report... 
> >
> > /Mattias
> >
> >
> > ----- Original Message -----
> > From: "Bjørn T Johansen" <btj@havleik.no>
> > > 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>
> > > > To: "Mattias Kregert" <mattias@kregert.se>
> > > > Cc: <pgsql-general@postgresql.org>
> > > > 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
> > > btj@havleik.no                  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
> > >
>
>

Re: I need a SQL...

From
Tom Lane
Date:
=?ISO-8859-1?Q?Bj=F8rn?= T Johansen <btj@havleik.no> writes:
> 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?

It'll probably work better if you quote the zero.  Unquoted, you get
some weird textual comparison.  Compare:

regression=# explain select * from time_tbl where f1-f1 >= 0;
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on time_tbl  (cost=0.00..1.14 rows=3 width=8)
   Filter: (((f1 - f1))::text >= '0'::text)
(2 rows)

regression=# explain select * from time_tbl where f1-f1 >= '0';
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on time_tbl  (cost=0.00..1.12 rows=3 width=8)
   Filter: ((f1 - f1) >= '00:00'::interval)
(2 rows)

In "C" locale, the textual comparison accidentally manages to give
the desired answers, but in other locales it would not.

(Just another example of why implicit coercions to text are evil.)

            regards, tom lane

Re: I need a SQL...

From
Bjørn T Johansen
Date:
You are absoletely correct, quoting the zero did the trick...
And my database has been intialized with Norwegian local, so I am
guessing that that's reason....

Thanks to all fo you... :)


BTJ


On Thu, 2003-09-11 at 16:26, Tom Lane wrote:
> =?ISO-8859-1?Q?Bj=F8rn?= T Johansen <btj@havleik.no> writes:
> > 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?
>
> It'll probably work better if you quote the zero.  Unquoted, you get
> some weird textual comparison.  Compare:
>
> regression=# explain select * from time_tbl where f1-f1 >= 0;
>                        QUERY PLAN
> --------------------------------------------------------
>  Seq Scan on time_tbl  (cost=0.00..1.14 rows=3 width=8)
>    Filter: (((f1 - f1))::text >= '0'::text)
> (2 rows)
>
> regression=# explain select * from time_tbl where f1-f1 >= '0';
>                        QUERY PLAN
> --------------------------------------------------------
>  Seq Scan on time_tbl  (cost=0.00..1.12 rows=3 width=8)
>    Filter: ((f1 - f1) >= '00:00'::interval)
> (2 rows)
>
> In "C" locale, the textual comparison accidentally manages to give
> the desired answers, but in other locales it would not.
>
> (Just another example of why implicit coercions to text are evil.)
>
>             regards, tom lane



Re: I need a SQL...

From
Adam Kavan
Date:
>
>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


How about SELECT CASE WHEN "StartTime" > "EndTime"  THEN
'23:59:99.99999999999'::time - "StartTime" + "EndTime" ELSE
"EndTime"-"StartTime" END FROM "TimeTable";

--- Adam Kavan
--- akavan@cox.net