drupal get node revision by 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