2

I have a module with a custom schema in it. On my cron job, I want to delete records older than 90 days. There is a date field in the table that holds a unix timestamp.

The query I want to run is DELETE FROM table WHERE UNIX_TIMESTAMP() - date > 778000, where 7780000 is the number of seconds in 90 days.

To test my query, I tried to make a select query with the same conditions:

$results = db_select('table')
->fields('table', array('field'))
->condition('UNIX_TIMESTAMP() - date', 7780000, '>')
->execute()->fetchAssoc();

However, the database AL doesn't parse the conditional the way I want it to:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'UNIX_TIMESTAMPdate' in 'where clause': SELECT ...

How do I write this conditional properly in the database AL?

4

You need to use the 'where' method for complex conditionals:

db_delete('table')
  ->where('UNIX_TIMESTAMP() - date > :date', array(':date' => 7780000))
  ->execute();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.