Failed mysql date query
I want to delete rows from a table that has a column more than 7200 secs old.
The Query
mysql_query("delete from logged where DATE_ADD ( log, INTERVAL $this->loginDuration SECOND) < NOW()",$this->link);
where:
name of table = logged;
name of column = log;
$this->loginDuration = 7200;
The value of log in db: 2011-06-25 09:56:51.
Todays date and time [ Now() ] : 2011-07-05 11:39:02
The query is meant to delete the row with log va开发者_如何学Golue 2011-06-25 09:56:51
because it is older than 7200 seconds but it does not.
What am I not getting right?
You have a space between DATE_ADD
and the parenthesis: ( log, ...
.
Use DATE_ADD( log, ...
From MySQL docs, Functions and Operators :
Note
By default, there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. However, spaces around function arguments are permitted.
And:
You can tell the MySQL server to accept spaces after function names by starting it with the
--sql-mode=IGNORE_SPACE
option. (See Section 5.1.6, “Server SQL Modes”.) Individual client programs can request this behavior by using theCLIENT_IGNORE_SPACE
option for mysql_real_connect(). In either case, all function names become reserved words.
I wanted to show you what I did (could not post this as a comment)
+----------------------+
| temp.d(table.column) |
+----------------------+
| 2011-07-05 22:08:20 |
| 2011-07-05 22:08:20 |
| 2011-07-05 22:08:21 |
| 2011-07-05 22:08:21 |
| 2011-07-05 22:08:22 |
| 2011-07-05 22:08:22 |
| 2011-07-05 22:08:23 |
| 2011-07-05 22:08:23 |
| 2011-07-05 22:08:24 |
| 2011-07-05 22:08:24 |
+----------------------+
10 rows in set (0.00 sec)
mysql> delete from temp where DATE_ADD(d, INTERVAL 1 SECOND) < NOW();
Query OK, 10 rows affected (0.01 sec)
精彩评论