Welcome to WebmasterWorld Guest from 34.201.121.213

Forum Moderators: open

Message Too Old, No Replies

Unable to select using datetime range

     
2:46 pm on Mar 31, 2010 (gmt 0)

Junior Member

10+ Year Member

joined:May 12, 2007
posts: 91
votes: 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!
4:30 pm on Mar 31, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2637
votes: 5


Wouldn't it be:

>= 'DATE_SUB(NOW(), INTERVAL 10 MINUTE)'
7:12 pm on Mar 31, 2010 (gmt 0)

Junior Member

10+ Year Member

joined:May 12, 2007
posts: 91
votes: 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.
8:39 pm on Mar 31, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


Demaestro is correct. you want >=. But backtick timestamp

... where `Timestamp` . . . .

It **may** be a reserved word, like datetime, int, etc.
12:57 pm on Apr 1, 2010 (gmt 0)

Junior Member

10+ Year Member

joined:May 12, 2007
posts:91
votes: 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...
}
9:17 pm on Apr 1, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 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.
2:46 am on Apr 2, 2010 (gmt 0)

Junior Member

10+ Year Member

joined:May 12, 2007
posts:91
votes: 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.
6:17 pm on Apr 2, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


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