Page 1 of 1

PHP: Time(Solved)

Posted: Wed Aug 10, 2005 8:59 am
by raghavan20
I have got a table ChatMessages_tbl with a field 'Time' in Mysql which is of datatype timestamp(14).

The values are inserted using "insert(NOW())"

Now, I want to delete entries in ChatMessages_tbl which are more than half an hour old from the current time.

I am looking for the syntax.

I tried this, but deletes even recent messages.

Code: Select all

$oldTime = date("YmdHis",time()) - (60*30);
$query = "delete from ChatMessages_tbl where `Time` < $oldTime";

Posted: Wed Aug 10, 2005 9:21 am
by hawleyjr
Try something like this:

Code: Select all

$query = "delete from ChatMessages_tbl where 
DATEDIFF(Time, now()) > 30";

Posted: Wed Aug 10, 2005 9:35 am
by raghavan20
This is wot it came up with
Error

SQL-query :

DELETE FROM `ChatMessages_tbl` WHERE DATEDIFF(

`Time` ,
NOW( )
) > 30

MySQL said:


#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( `Time` ,
NOW( ) ) > 30' at line 1

Posted: Wed Aug 10, 2005 11:01 am
by raghavan20
I have found this query which works:

Code: Select all

delete from `ChatMessages_tbl` where (TIME_TO_SEC(NOW()) - TIME_TO_SEC(`Time`)) > (60*30)