How add a function in a condition method in db api

Question

I am strugginling to find out how to execute the below query using drupal's db api

$startdate = '1491001200';
$enddate = '1498690799';
"SELECT * FROM field_data_field_symposium_event_time
    WHERE UNIX_TIMESTAMP(field_symposium_event_time_value) BETWEEN
    $startdate AND $enddate;"

I have tried adding 'addExpression' but does not work. I did it like this

$query = db_select('node','n');
$query->join('field_data_field_symposium_event_time', 'et', 'et.entity_id = n.nid');    
$query->condition('et.field_data_field_symposium_event_time_value',array($day_start, $day_end), 'BETWEEN'));
$query->addExpression('UNIX_TIMESTAMP(et.field_symposium_event_time_value)');
$query->fields('et',array('field_symposium_event_time_value'));
$result = $query->execute()->fetchAll();

The $result is empty. Is there a way to achieve this as I dont want to write the full query .

Answer 1

I believe the addExpression is for SELECT clause expressions

https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21Select.php/function/Select%3A%3AaddExpression/8.2.x

For a WHERE clause expression it should be

$query->where('UNIX_TIMESTAMP(et.field_symposium_event_time_value) BETWEEN :start AND :end', array(':start' => $day_start, ':end' => $day_end));

https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21QueryConditionTrait.php/function/QueryConditionTrait%3A%3Awhere/8.2.x

And then of course remove your $query->condition(...); line.

Answer 2

I think that ->addExpression is about adding SQL snippets like yours to modify selected columns right after the SELECT (vs. adding expressions to a WHERE clause).

The ->where() (and ->having()) clauses are what you want.