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?