Hi.
On Mon 2002-07-29 at 14:41:30 -0400, [EMAIL PROTECTED] wrote:
> Sorry to repost but I typed in the wrong sql statement in my previous post.
Ah. Okay.
> For some reason the below statement is not working. Can anyone tell me why?
>
> Select EDIT_LOCK from ordmaster where EDIT_LOCK + INTERVAL 10 MINUTE < now()
> AND ORDER_NO = '5' AND EDIT_LOCK > 0;
Regardless of the original problem, you should use
EDIT_LOCK < NOW() - INTERVAL 10 MINUTE
because this variant has no expression on the left side and therefore
could use an index, if there is one on EDIT_LOCK (MySQL does not
optimize expressions, in the few cases where this would be
possibible).
> -EDIT_LOCK is a MySQL (ver 3.23.49-max) time field, allows nulls, default is
> NULL.
Oh. Is it really a TIME field, not TIMESTAMP? In this case you would
compare a time (without date) with a whole datetime value from
NOW(). These values cannot be compared reasonable.
Additionally +- INTERVAL only works on DATE or DATETIME/TIMESTAMP
values, otherwise you have to use DATE_SUB/DATE_ADD.
In this case, you would need something like
SELECT EDIT_LOCK
FROM ordmaster
WHERE EDIT_LOCK < DATE_FORMAT( NOW() - INTERVAL 10 MINUTE, "%T" )
AND ORDER_NO = '5' AND EDIT_LOCK > 0;
This does not handle the special cases on day change, but I presume
that this already had been thought of, or else a TIME instead of a
DATETIME field makes no sense.
Greetings,
Benjamin.
--
[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
