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?

Regards,
Thomas