• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2502
  • Last Modified:

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:

  $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(); 

Open in new window


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, '>=');

Open in new window


It works fine.  But this returns nodes with a pubdate value greater than right now, which I don't want.
0
michaelgiaimo
Asked:
michaelgiaimo
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
With time values, I would do two anyway. So:

$query->condition('f.field_pubdate_value', REQUEST_TIME - 172800, '>=');
$query->condition('f.field_pubdate_value', REQUEST_TIME, '<=');

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
By the way, your syntax appears correct per documentation: http://drupal.org/node/310086
Not sure why it did not work. Might be something with how it is handling the conversion of the date/time when it hits MySQL. You may need to format those, so they pass in as YYYY-MM-DD formatted strings.
0
 
michaelgiaimoAuthor Commented:
Yeah, that was it - field_pubdate_value was a mysql date field, while REQUEST_TIME returns the Unix time.  Good catch.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now