how to use function of My Sql in hook view query alter in drupal7

Question

I am using views_query_alter for alter query of view. In my query I want condition like DATE_FORMAT(FROM_UNIXTIME(field_data_field_hiddeneventdate.field_hiddeneventdate_value), '%m') = '06'.

But when I am trying

    $condition = array('field' =>   "DATE_FORMAT(FROM_UNIXTIME(field_data_field_hiddeneventdate.field_hiddeneventdate_value),   '%m')",
'value' => date('%Y-%m'), //$view->exposed_raw_input['title'],
'operator' => '>=',
);

It generate query :

SELECT node.title AS node_title, node.nid AS nid, field_data_field_hiddeneventdate.field_hiddeneventdate_value AS node_changed
FROM 
{node} node
LEFT JOIN {field_data_field_hiddeneventdate} field_data_field_hiddeneventdate ON node.nid = field_data_field_hiddeneventdate.entity_id AND (field_data_field_hiddeneventdate.entity_type = node AND field_data_field_hiddeneventdate.deleted = 0)
WHERE (( (node.status = '1') AND (node.type IN  ('events_meeting')) )AND( (DATE_FORMATFROM_UNIXTIMEfield_data_field_hiddeneventdate.field_hiddeneventdate_valuem >= '%2013-%06') ))
LIMIT 10 OFFSET 0.

But I want :

SELECT node.title AS node_title, node.nid AS nid, field_data_field_hiddeneventdate.field_hiddeneventdate_value AS node_changed,
DATE_FORMAT(FROM_UNIXTIME(field_data_field_hiddeneventdate.field_hiddeneventdate_value), '%d-%m-%Y') AS dt
FROM node node
LEFT JOIN field_data_field_hiddeneventdate field_data_field_hiddeneventdate 
ON 
node.nid = field_data_field_hiddeneventdate.entity_id 
AND 
(field_data_field_hiddeneventdate.entity_type = 'node' 
AND field_data_field_hiddeneventdate.deleted = 0)
WHERE (( (node.status = '1') AND (node.type IN  ('events_meeting')) )
AND(           DATE_FORMAT(FROM_UNIXTIME(field_data_field_hiddeneventdate.field_hiddeneventdate_value), '%m') = '06' 
    )
)
LIMIT 10 OFFSET 0;

Can anyone help me.

Answer 1

If you want to change the query for a view, why not just not use the view and do a db_select. Direct db queries are faster than views in Drupal.