How to use CURDATE() and INTERVAL in Drupal Database object syntax How to convert SQL query to Drupal Database object query. I have already tried many ways but I didn't get success.

# Get the records which are published in week
SELECT node.nid as nid 
FROM node node
INNER JOIN field_date field_date ON node.nid = field_date.entity_id
WHERE from_unixtime(field_date.field_date_value) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE();


The above query is working fine in mysql.
Try db_query() function:
Solution -1 

$query = 'SELECT node.nid as nid FROM node node INNER JOIN field_date field_date ON node.nid = field_date.entity_id WHERE from_unixtime(field_date.field_date_value) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE()';
$result = db_query($query)->fetchAll();

Solution-2



$query = db_select('node', 'n');
$query->fields('n', array());
$query->innerJoin('field_date', 'fd', 'n.nid = fd.entity_id');
$query->where('FROM_UNIXTIME(fd.field_date_value) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE()');
$result = $query->execute();