[prev in list] [next in list] [prev in thread] [next in thread] 

List:       php-db
Subject:    RE: [PHP-DB] time field query problems.
From:       Steve Bradwell <SteveB () compxnet ! com>
Date:       2002-07-30 12:09:15
[Download RAW message or body]

Thanks,
I wasn't sure what kind of field to use, I'll give the timestamp a try and
set 0 as default.

Regards,
Steve.


-----Original Message-----
From: DL Neil [mailto:PHPml@DandE.HomeChoice.co.uk]
Sent: Monday, July 29, 2002 6:34 PM
To: Steve Bradwell; mysql@lists.mysql.com; Php-Db (E-mail)
Subject: Re: [PHP-DB] time field query problems.


Steve,

> 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;
>
> -EDIT_LOCK is a MySQL (ver 3.23.49-max) time field, allows nulls, default
is
> NULL.
>
> If this cannot be done in a query, whats the best way to compare time in
> php?


The best way to compare time in PHP is to use the MySQL RDBMS that is
managing/retrieving the data for you.

Recommendation 1: do not use a Time field (you did mean the back 'half' of a
Date-time field didn't you?). Because you are (apparently only) using this
field to temporarily lock a row, the value is only ever used for computation
(cf display). A Timestamp field is best for computation - a Time field for
presentation. Consider also storing such data as an integer field or beware
the automatic update feature for Timestamp fields.

Recommendation 2: re-consider the (default) use of NULL - this may be the
root of the question you're asking: what if the row has never been 'locked'
and attempt the (above) SELECT? (then the last comparison clause would be
illogical) If the default were zero (0 or 00:00:00) and the retrieval logic
updated slightly, things should be less complicated.

Regards,
=dn



---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <mysql-thread115837@lists.mysql.com>
To unsubscribe, e-mail
<mysql-unsubscribe-SteveB=compxnet.com@lists.mysql.com>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[prev in list] [next in list] [prev in thread] [next in thread]