Unable to select using datetime range

Forum Moderators: open

Message Too Old, No Replies

Unable to select using datetime range

techtheatre

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

4:30 pm on Mar 31, 2010 (gmt 0)

Wouldn't it be:

>= 'DATE_SUB(NOW(), INTERVAL 10 MINUTE)'

techtheatre

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

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

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

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

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

6:17 pm on Apr 2, 2010 (gmt 0)

LOL . . . I can't believe I didn't see the quotes. Ah well. :-P