Thread

1. [SQL] Time related question...

   Kevin Colagio <kcolagio@wc.eso.mc.xerox.com> — 1998-06-17T05:33:29Z

   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>
   
   

2. Re: [SQL] Time related question...

   Patrice Hédé <patrice@idf.net> — 1998-06-17T12:45:15Z

   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/ ----------------------------------
   
   
   

3. Re: [SQL] Time related question...

   Herouth Maoz <herouth@oumail.openu.ac.il> — 1998-06-17T13:02:15Z

   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
   
   
   
   

4. Re: [SQL] Time related question...

   Herouth Maoz <herouth@oumail.openu.ac.il> — 1998-06-17T13:17:15Z

   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