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 bt*@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."
----------------------------------------------------------------------------------------------- 25 2661
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 isnot 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
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
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
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
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" <bt*@havleik.no>
To: "Mattias Kregert" <ma*****@kregert.se>
Cc: <pg***********@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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
dsfdfd
----- 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:
Yes, the period can exceed 12 hours, so what you are saying is that this isnot 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...
BTJdsfdf
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" <bt*@havleik.no> To: "Mattias Kregert" <ma*****@kregert.se> Cc: <pg***********@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 bt*@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
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 isnot 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
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
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" <bt*@havleik.no>
To: "Mattias Kregert" <ma*****@kregert.se>
Cc: <pg***********@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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
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" <bt*@havleik.no> To: "Mattias Kregert" <ma*****@kregert.se> Cc: <pg***********@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 bt*@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
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" <bt*@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" <bt*@havleik.no> To: "Mattias Kregert" <ma*****@kregert.se> Cc: <pg***********@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 bt*@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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
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" <bt*@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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
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" <bt*@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" <bt*@havleik.no> To: "Mattias Kregert" <ma*****@kregert.se> Cc: <pg***********@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 bt*@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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
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" <bt*@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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
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" <bt*@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" <bt*@havleik.no> To: "Mattias Kregert" <ma*****@kregert.se> Cc: <pg***********@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 bt*@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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
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" <bt*@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 >= 0then 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" <bt*@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 doyou use? > > Are you absolutely sure you copied it exactly? You typed in '>=' and not '=', right? > > /Mattias > > ----- Original Message ----- > From: "Bjørn T Johansen" <bt*@havleik.no> > To: "Mattias Kregert" <ma*****@kregert.se> > Cc: <pg***********@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 problemis 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 equalor 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 youassume all the data is > > > > good), then stop times that are less than start timeswould indicate an > > > > intervening midnight. > > > > > > > > The dates do not have to be in the same fields as thetimes, since you can add > > > > date and time data to create a timestamp for datetimecalculations: > > > > > > > > (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 bt*@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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
=?ISO-8859-1?Q?Bj=F8rn?= T Johansen <bt*@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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
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" <bt*@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" <bt*@havleik.no> To: "Mattias Kregert" <ma*****@kregert.se> Cc: <pg***********@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 bt*@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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
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" <bt*@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 >= 0then 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" <bt*@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 doyou use? > > Are you absolutely sure you copied it exactly? You typed in '>=' and not '=', right? > > /Mattias > > ----- Original Message ----- > From: "Bjørn T Johansen" <bt*@havleik.no> > To: "Mattias Kregert" <ma*****@kregert.se> > Cc: <pg***********@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 problemis 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 equalor 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 youassume all the data is > > > > good), then stop times that are less than start timeswould indicate an > > > > intervening midnight. > > > > > > > > The dates do not have to be in the same fields as thetimes, since you can add > > > > date and time data to create a timestamp for datetimecalculations: > > > > > > > > (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 bt*@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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
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 <bt*@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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
=?ISO-8859-1?Q?Bj=F8rn?= T Johansen <bt*@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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
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 <bt*@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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org 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
--- ak****@cox.net
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match 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
--- ak****@cox.net
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Sofia |
last post by:
My name is Sofia and I have for many years been running a personals site,
together with my partner, on a non-profit basis. The site is currently not
running due to us emigrating, but during its...
|
by: Robert Maas, see http://tinyurl.com/uh3t |
last post by:
System login message says PHP is available, so I tried this:
http://www.rawbw.com/~rem/HelloPlus/h.php
It doesn't work at all. Browser just shows the source.
What am I doing wrong?
|
by: Gregory Nans |
last post by:
hello,
i need some help to 'tree-ify' a string...
for example i have strings such as :
s = """A(here 's , B(A ) silly test) C(to show D(what kind) of stuff i
need))"""
and i need to...
|
by: Mike Kamermans |
last post by:
I hope someone can help me, because what I'm going through at the moment
trying to edit XML documents is enough to make me want to never edit XML
again.
I'm looking for an XML editor that has a...
|
by: JustSomeGuy |
last post by:
I need to write an new class derived from the list class.
This class stores data in the list to the disk if an object
that is added to the list is over 1K in size.
What methods of the std stl...
|
by: Bob.Henkel |
last post by:
I write this to tell you why we won't use postgresql even though we wish we
could at a large company. Don't get me wrong I love postgresql in many
ways and for many reasons , but fact is fact. If...
|
by: Michael R. Pierotti |
last post by:
Dim reg As New Regex("^\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}$")
Dim m As Match = reg.Match(txtIPAddress.Text)
If m.Success Then
'No need to do anything here
Else
MessageBox.Show("You need to enter a...
|
by: skumar434 |
last post by:
i need to store the data from a data base in to structure
.............the problem is like this ....suppose there is a data base
which stores the sequence no and item type etc ...but i need only...
|
by: Alan Mailer |
last post by:
A project I'm working on is going to use VB6 as a front end. The back
end is going to be pre-existing MS Access 2002 database tables which
already have records in them *but do not have any...
|
by: U S Contractors Offering Service A Non-profit |
last post by:
Brilliant technology helping those most in need Inbox
Reply
U S Contractors Offering Service A Non-profit
show details
10:37 pm (1 hour ago)
Brilliant technology helping those most in need ...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |