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