Re: time field query problems.

Hi. On Mon 2002-07-29 at 14:41:30 -0400, SteveB@compxnet.com 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. -- benjamin-mysql@pflugmann.de