drupal get node revision by timestamp I'm building a module that gets the value of a custom field based on a timestamp that a user clicked on it in the past.

To not make it too specific and complex I simplified my question so other users can use this as well.

Basically I'm selecting from the pop_links_stats ( has the timestamp ), node, node_revision, and field_revision_field_custom tables:

$query2 = db_select('pop_links_stats', 'p');    
$query2-&gt;join('node', 'n', 'n.nid = p.nid');
$query2-&gt;join('node_revision', 'nr', 'nr.nid = n.nid');
$query2-&gt;join('field_revision_field_custom', 'f', 'nr.vid = f.revision_id');


I already have the $nid so I set the condition to that $nid

$query2-&gt;condition('n.nid', $node-&gt;nid);


grouping by so it will only select one 'click' per day, per node, per ip ( added the following for the completeness. I need this but it is somewhat unrelated to my question so ignore the groupBy if this is not causing any problems. )  

$query2-&gt;groupBy('DATE(FROM_UNIXTIME(p.timestamp))')-&gt;groupBy('n.nid')-&gt;groupBy('p.hostname');


selecting the fields I need ( for now just the revision id) :

$query2-&gt;fields('nr', array('vid'));


So my guess is to join the node_revision table and selecting the max vid where nr.timestamp &lt;= p.timestamp and n.nid = nr.nid
OR 'selecting' the max nr.timestamp where nr.timestamp &lt;= p.timestamp

But I'm having trouble implementing that in the drupal DB API

EDIT:

Here's a query I've used in mySQL which gives me the result I want:

SELECT p.cid, FROM_UNIXTIME(p.timestamp), FROM_UNIXTIME(nrev.timestamp), n.nid, nrev.vid FROM pop_links_stats p
JOIN node n ON n.nid = p.nid
JOIN node_revision nrev ON nrev.vid = (SELECT max(vid) FROM node_revision nr WHERE nr.nid = p.nid AND nr.timestamp &lt;= p.timestamp )
GROUP BY DATE(FROM_UNIXTIME(p.timestamp)), p.nid, p.hostname


So what I'm looking for is how to make the join in:

JOIN node_revision nrev ON nrev.vid = (SELECT max(vid) FROM node_revision nr WHERE nr.nid = p.nid AND nr.timestamp &lt;= p.timestamp )
There doesn't seem to be a structured way to shoehorn an advanced join like that into an API query...so you'll probably just have to go manually:

$query = db_select('pop_links_stats', 'p')
  -&gt;fields('p', array('cid'))
  -&gt;fields('n', array('nid'))
  -&gt;fields('nrev', array('vid'))
  -&gt;groupBy('DATE(FROM_UNIXTIME(p.timestamp))')
  -&gt;groupBy('p.nid')
  -&gt;groupBy('p.hostname');

$query-&gt;addExpression('FROM_UNIXTIME(p.timestamp)', 'p_timestamp');
$query-&gt;addExpression('FROM_UNIXTIME(nrev.timestamp)', 'nrev_timestamp');

$query-&gt;join('node', 'n', 'n.nid = p.nid');
$query-&gt;join('node_revision', 'nrev', 'nrev.vid = (SELECT max(vid) FROM {node_revision} nr WHERE nr.nid = p.nid AND nr.timestamp &lt;= p.timestamp )');


That produces:

SELECT p.cid AS cid, n.nid AS nid, nrev.vid AS vid, FROM_UNIXTIME(p.timestamp) AS p_timestamp, FROM_UNIXTIME(nrev.timestamp) AS nrev_timestamp
FROM 
{pop_links_stats} p
INNER JOIN {node} n ON n.nid = p.nid
INNER JOIN {node_revision} nrev ON nrev.vid = (SELECT max(vid) FROM node_revision nr WHERE nr.nid = p.nid AND nr.timestamp &lt;= p.timestamp )
GROUP BY DATE(FROM_UNIXTIME(p.timestamp)), p.nid, p.hostname