Thread: [SQL] Time related question...

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>

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

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

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