pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed[SQL] Time related question...
4+ messages / 3 participants
[nested] [flat]
* [SQL] Time related question...
@ 1998-06-17 05:33 Kevin Colagio <kcolagio@wc.eso.mc.xerox.com>
0 siblings, 3 replies; 4+ messages in thread
From: kcolagio@wc.eso.mc.xerox.com @ 1998-06-17 05:33 UTC (permalink / raw)
To: pgsql-sql
I have a database that tracks the service calls we do. In the database
are abstime values for:
dateentered
dateclosed
dateassigned
datecontacted
I have perl scripts managing the interaction between our web site and the
database.
My question is: what is the SQL statement that will allow me to find:
1) a list of the calls where the difference between the dateentered and
dateclosed is less than (say) 3 days.
I have tried:
Select * from servicecall where (dateentered <#> dateclosed) #<= 3 days;
Select * from servicecall where (dateclosed - dateentered) #<= 3 days;
Select * from servicecall where (dateentered <#> dateclosed) #<= @3 days;
Select * from servicecall where (dateclosed - dateentered) #<= @3 days;
Any suggestions?
Thanks.
--
Kevin Colagio, Systems Administrator, Webmaster,
and perpetual student.
kcolagio@wc.eso.mc.xerox.com
Personal URL:<http://www.rit.edu/~kdc5072;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] Time related question...
@ 1998-06-17 12:45 Patrice Hédé <patrice@idf.net>
parent: Kevin Colagio <kcolagio@wc.eso.mc.xerox.com>
2 siblings, 0 replies; 4+ messages in thread
From: Patrice Hédé @ 1998-06-17 12:45 UTC (permalink / raw)
To: Kevin Colagio <kcolagio@wc.eso.mc.xerox.com>; +Cc: pgsql-sql
On Wed, 17 Jun 1998, Kevin Colagio wrote:
>
> I have a database that tracks the service calls we do. In the database
> are abstime values for:
> dateentered
> dateclosed
> dateassigned
> datecontacted
Can't you design it with 'datetime' fields instead, which is much better
handled ? then, you would be able to do exactly what you want !
> My question is: what is the SQL statement that will allow me to find:
> 1) a list of the calls where the difference between the dateentered and
> dateclosed is less than (say) 3 days.
>
> I have tried:
> Select * from servicecall where (dateentered <#> dateclosed) #<= 3 days;
> Select * from servicecall where (dateclosed - dateentered) #<= 3 days;
> Select * from servicecall where (dateentered <#> dateclosed) #<= @3 days;
> Select * from servicecall where (dateclosed - dateentered) #<= @3 days;
with datetime, it could be then :
select * from servicecall whre (dateclosed - dateentered) < '3 days';
However, if you really need abstime, I don't know the answer.
Hope this helps
Patrice
--
Patrice HÉDÉ --------------------------------- patrice@idf.net -----
... Ásólfr hljóp upp á skip Hrúts ok varð fjögurra manna bani, áðr
Hrútr varð varr við. Sneri hann þá í móti honum. En er þeir fundust,
lagði Ásólfr í skjöld Hrúts ok í gegnum, en Hrútr hjó til Ásólfs, ok
varð þat banahögg. --- Njáls Saga
----- http://www.idf.net/patrice/ ----------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] Time related question...
@ 1998-06-17 13:02 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Kevin Colagio <kcolagio@wc.eso.mc.xerox.com>
2 siblings, 0 replies; 4+ messages in thread
From: Herouth Maoz @ 1998-06-17 13:02 UTC (permalink / raw)
To: kcolagio@wc.eso.mc.xerox.com; pgsql-sql
At 15:33 +0300 on 17/6/98, Kevin Colagio wrote:
>
> My question is: what is the SQL statement that will allow me to find:
> 1) a list of the calls where the difference between the dateentered and
> dateclosed is less than (say) 3 days.
Here is an example:
testing=> \d example4
Table = example4
+--------------------------+------------------------------+-------+
| Field | Type | Length|
+--------------------------+------------------------------+-------+
| entered | datetime | 8 |
| closed | datetime | 8 |
+--------------------------+------------------------------+-------+
testing=> SELECT * FROM example4;
entered |closed
----------------------------+----------------------------
Wed Jan 14 00:00:00 1998 IST|Thu Jan 15 00:00:00 1998 IST
Wed Jan 14 00:00:00 1998 IST|Sun Jan 18 00:00:00 1998 IST
Thu Mar 19 23:00:00 1998 IST|Tue Feb 17 00:00:00 1998 IST
Wed Apr 01 00:00:00 1998 IDT|Thu Apr 01 00:00:00 1999 IST
Mon Jun 01 00:00:00 1998 IDT|Wed Jun 03 00:00:00 1998 IDT
(5 rows)
testing=> SELECT *
testing-> FROM example4
testing-> WHERE (closed - entered) < '3 days';
entered |closed
----------------------------+----------------------------
Wed Jan 14 00:00:00 1998 IST|Thu Jan 15 00:00:00 1998 IST
Thu Mar 19 23:00:00 1998 IST|Tue Feb 17 00:00:00 1998 IST
Mon Jun 01 00:00:00 1998 IDT|Wed Jun 03 00:00:00 1998 IDT
(3 rows)
If you're wondering why you got the second row, it's because it has an
entered date which is greater than the closed date. A negative difference
is less than 3 days... So you should actually make sure this doesn't
happen, like this:
testing=> SELECT *
testing-> FROM example4
testing-> WHERE closed > entered
testing-> AND (closed - entered) < '3 days';
entered |closed
----------------------------+----------------------------
Wed Jan 14 00:00:00 1998 IST|Thu Jan 15 00:00:00 1998 IST
Mon Jun 01 00:00:00 1998 IDT|Wed Jun 03 00:00:00 1998 IDT
(2 rows)
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] Time related question...
@ 1998-06-17 13:17 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Kevin Colagio <kcolagio@wc.eso.mc.xerox.com>
2 siblings, 0 replies; 4+ messages in thread
From: Herouth Maoz @ 1998-06-17 13:17 UTC (permalink / raw)
To: patrice@idf.net, Kevin Colagio <kcolagio@wc.eso.mc.xerox.com>; +Cc: pgsql-sql
At 15:45 +0300 on 17/6/98, Patrice Hédé wrote:
> with datetime, it could be then :
>
> select * from servicecall whre (dateclosed - dateentered) < '3 days';
>
> However, if you really need abstime, I don't know the answer.
Actually, all that needs be done is to cast the abstime as datetime. Such as:
SELECT *
FROM servicecall
WHERE ( datetime( dateclosed ) - datetime( dateentered ) ) < '3 days';
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1998-06-17 13:17 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-06-17 05:33 [SQL] Time related question... Kevin Colagio <kcolagio@wc.eso.mc.xerox.com>
1998-06-17 12:45 ` Patrice Hédé <patrice@idf.net>
1998-06-17 13:02 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-06-17 13:17 ` Herouth Maoz <herouth@oumail.openu.ac.il>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox