At 11:52 PM 21/09/2007, you wrote:

>Hey all,
>
>I've started work on the web application I hope to be able to launch
>once my earlier mentioned performance issues have been cleared out. A
>part of this application needs to pull some data based on a few TIMESTAMPS.
>
>The table (CALLSTAT) looks like this:
>
>CALLSTART TIMESTAMP Not Null
>CALLANSWER TIMESTAMP Nullable
>CALLEND TIMESTAMP Nullable
>ENDSTATE SMALLINT Nullable
>TIMESLOT SMALLINT Nullable
>AGENT VARCHAR(30) Nullable
>
>The format of the TIMESTAMP in CALLSTART and CALLANSWER is: 2007-09-21
>12:58:45.0000
>
>Basically I need to pull all the rows where CALLSTART is after
>2007-09-21 08:00:00 and CALLANSWER - CALLSTART < 10 seconds.
>
>I've tried my hand with the following SQL:
>
>SELECT * FROM CALLSTAT WHERE CALLSTART>'2007-09-21 08:00:00:00.0000' AND
>(CALLANSWER-CALLSTART)<10;
>
>This yields all the rows for the given day. Every single one of them,
>even those where the difference between CALLSTART and CALLANSWER is way
>beyond 10 seconds.
>
>I then tried this:
>
>SELECT FIRST 1 (CALLANSWER-CALLSTART), CALLSTART, CALLANSWER FROM CALLSTAT;
>
>And got this in return:
>
>
>CALLSTART CALLANSWER
>===================== =========================
>=========================
>0.000057870 2007-09-21 08:00:02.0000
> 2007-09-21 08:00:07.0000
>
>The difference is 5 seconds, but the result is shown as 0.000057870.
>Hmmmm.. I then tried this:
>
>SELECT * FROM CALLSTAT WHERE CALLSTART>'2007-09-21 08:00:00:00.0000' AND
>(CALLANSWER-CALLSTART)<0.000120000;
>
>And magically it fetched a bunch of rows which so-so matched the 10
>second criteria.
>
>Anybody know what's going on here? Is this some microtime thing? Is
>there a more correct way to query these TIMESTAMP columns, than the one
>I've used?

Timestamp - timestamp gives you a result in DAYS So you need to
convert your comparison operand to seconds to match what it is being
compared to:

SELECT * FROM CALLSTAT
WHERE CALLSTART>'2007-09-21 08:00:00:00.0000'
AND (86400 * (CALLANSWER-CALLSTART)) < 10;

./hb