michaelgiaimo
asked on
Drupal db_select with BETWEEN not working
I am trying to build a list of nodes that have a publication date between two days ago and now. I tried this:
But it's not working - now, it's just the one row with the BETWEEN clause (Line 7) - if I replace with this:
It works fine. But this returns nodes with a pubdate value greater than right now, which I don't want.
$query = db_select('node', 'n');
$query->join('field_data_field_pubdate', 'f', 'n.nid = f.entity_id'); //JOIN node with pubdate
$query->fields('n', array('nid', 'created', 'title'));
$query->addField('f', 'field_pubdate_value', 'pubdate');
$query->condition('n.type', variable_get('ain_sitemap_node_types', array_keys(node_type_get_names())));
$query->condition('n.status', NODE_PUBLISHED);
$query->condition('f.field_pubdate_value', array(REQUEST_TIME - 172800, REQUEST_TIME), 'BETWEEN');
$query->addTag('node_access');
$query->orderBy('f.field_pubdate_value', 'DESC');
$query->range(0, 1000);
$nodes = $query->execute()->fetchAll();
But it's not working - now, it's just the one row with the BETWEEN clause (Line 7) - if I replace with this:
$query->condition('f.field_pubdate_value', REQUEST_TIME - 172800, '>=');
It works fine. But this returns nodes with a pubdate value greater than right now, which I don't want.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yeah, that was it - field_pubdate_value was a mysql date field, while REQUEST_TIME returns the Unix time. Good catch. Thanks!
Open in new window