| Unable to select using datetime range
|
techtheatre
msg:4107602 | 2:46 pm on Mar 31, 2010 (gmt 0) |
I am using MySql (and PHP) to try to find out if there are any records in my table that are less than 10 minutes old. Basically this is part of a time-keeping script, and I create a new entry if it has been more than 10 minutes (no results) or I update the current entry if it is less than 10 minutes.
Basic table structure (unimportant fields removed):
CREATE TABLE `timesheet` (
`RecordId` int(8) NOT NULL auto_increment,
`Timestamp` datetime NOT NULL,
PRIMARY KEY (`RecordId`)
) AUTO_INCREMENT=56 ;
INSERT INTO `timesheet` VALUES (55, '2010-03-30 18:19:00');
So, I am using this query:
SELECT * FROM timesheet WHERE Timestamp<='DATE_SUB(NOW(), INTERVAL 10 MINUTE)' ORDER BY Timestamp DESC LIMIT 1;
The problem is that it always returns my latest record...even if it is older than 10 minutes.
What am i doing wrong? Thanks!
|
Demaestro
msg:4107660 | 4:30 pm on Mar 31, 2010 (gmt 0) |
Wouldn't it be:
>= 'DATE_SUB(NOW(), INTERVAL 10 MINUTE)'
|
techtheatre
msg:4107773 | 7:12 pm on Mar 31, 2010 (gmt 0) |
I have tried switching the < and > but when i do it never finds a result...even if the time is well under 10 minutes. I just tried again in case i was being brain-dead...but still no luck.
|
rocknbil
msg:4107829 | 8:39 pm on Mar 31, 2010 (gmt 0) |
Demaestro is correct. you want >=. But backtick timestamp
... where `Timestamp` . . . .
It **may** be a reserved word, like datetime, int, etc.
|
techtheatre
msg:4108218 | 12:57 pm on Apr 1, 2010 (gmt 0) |
I have added backticks...but it still does not work. It finds no results. If i change the > to a < then it finds one result. Here is my full query:
//check to see if we have an active (live) entry in the database
$sql = "SELECT * FROM timesheet WHERE `UserId`='$UserId' AND `TimeType`='out' AND `Timestamp`>='DATE_SUB(NOW(), INTERVAL 10 MINUTE)' ORDER BY `Timestamp` DESC LIMIT 1; " ;
$result = mysql_query($sql, $db);
if( mysql_num_rows($result)==0 )
{
//no live entry, so create a new one
$sql = "INSERT INTO timesheet...
}
|
rocknbil
msg:4108568 | 9:17 pm on Apr 1, 2010 (gmt 0) |
What happens when you do . . .
$result = mysql_query($sql, $db) or die(mysql_error());
?
That query should work . . . test it like so
select now();
-> 2010-04-01 14:10:06
select '2010-04-01 14:10:06' >= date_sub(now(),interval 10 minute);
--> 1
When 10 minutes passes, it will return 0. The only thing that can be assumed is one of the other values are incorrect, that is, in `UserId`='$UserId', the actual value of $UserID is not in the database.
On that topic, if $UserID is numeric, remove the quotes when you add the "or die." It may reveal something you're assuming. :-) Numeric values do not need to be quoted, but when quoted, you might be querying . . . where `UserId`='' . . . which will not error. It will also return no results if all fields are populated.
|
techtheatre
msg:4108699 | 2:46 am on Apr 2, 2010 (gmt 0) |
Unfortunately no errors were occouring...and the UserId is actually character based...so these did not resolve it. One thing you said did however solve it (after MUCH beating my head against a wall). I needed to remove the quote marks from around the "date_sub" command.
I started here:
SELECT * FROM timesheet WHERE Timestamp<='DATE_SUB(NOW(), INTERVAL 10 MINUTE)' ORDER BY Timestamp DESC LIMIT 1;
And ended up here:
SELECT * FROM timesheet WHERE Timestamp<=DATE_SUB(NOW(), INTERVAL 10 MINUTE) ORDER BY Timestamp DESC LIMIT 1;
Thanks for the help along the way. So frustrating when things turn out to be so simple...but very happy it now seems to be working.
|
rocknbil
msg:4109016 | 6:17 pm on Apr 2, 2010 (gmt 0) |
LOL . . . I can't believe I didn't see the quotes. Ah well. :-P
|
|
|
