Posted by Tom Rogers on 03/24/05 15:18

Hi,

Thursday, March 24, 2005, 6:50:38 PM, you wrote:
J> Dear all

J> Please, I realy need your help. I am trying to extract only those records
J> that were entered during the past hour from my MySQL database. The
J> following SQL statement extracts all the records. As soon as I change the
J> INTERVAL to 1 HOUR I see all the records in my database. When I change
J> CURDATE() to CURTIME() no records are extracted from the database. The
J> cgtimeposted field is a time data type.

J> Please advise me as to how I can formulate my SQL statement so that only
J> those records entered within the past hour displays on my page.

J> Here is my current SQL statement - this statement displays all the records:

J> $sql = "Select tblchatglobal.cgid, tblchatglobal.cgdateposted,
J> tblchatglobal.cgtimeposted, tblchatglobal.uid, tblchatglobal.cgmsg,
J> tblusers.uid, tblusers.uusername from tblchatglobal, tblusers where
J> (tblchatglobal.uid = tblusers.uid) and
J> (DATE_SUB(CURDATE(),INTERVAL 1 HOUR)
J> <= tblchatglobal.cgtimeposted) order by
J> tblchatglobal.cgtimeposted desc";

J> Jacques

You cannot do this with just a time field as what happens at midnight
and now() becomes 00:00:00, you will need to work with a full date_time
field.

The other problem is CURDATE has no time component, it is just a date
so subtracting 1 hour will always return 23:00:00.

So change the time field to a timestamp and try

.... AND tblchatglobal.cgtimeposted > (DATE_SUB(NOW(),INTERVAL 1 HOUR))

--
regards,
Tom

[Back to original message]