Answers.Body: <p>This is working in beta6 of CiviCRM entity...probably before, can't remember exactly when I fixed this.</p>
  
  <p>Relevant D.O issue <a href="https://www.drupal.org/node/2570319" rel="nofollow noreferrer">https://www.drupal.org/node/2570319</a></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-04-18T08:48:06.240Z
Answers.Text: That's great. Thanks for providing an update on this.
Answers.UserDisplayName: William Mortada
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-26T19:53:54.590Z
Answers.Text: Interestingly, using 7.x-2.1, the problem appears for activities - i.e. the activity date time is getting mangled with FROM_UNIXTIME(civicrm_activity.activity_date_time). It was okay on 7.x-2.0-beta11
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-26T20:02:38.500Z
Answers.Text: Can you make an issue at the d.o project page issue queue? That will help me remember
Answers.UserDisplayName: jackrabbithanna
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-04-18T01:23:59.477Z
Answers.LastActivityDate: 2017-04-18T01:23:59.477Z
Answers.UserDisplayName: jackrabbithanna
Body: <p>I am trying to use Drupal Views to create a report of contributions.  I would like the user to be able to filter the contributions by financial type and by date.  </p>
  
  <p>To allow the user filter by date using a simple drop down I have installed the <a href="https://www.drupal.org/project/civicrm_entity" rel="nofollow">civicrm_entity</a> module.  I have previously used this module to create views for activities and events and was able to filter these by date.  However this doesn't work with contributions for some reason.</p>
  
  <p>Using the <a href="https://www.drupal.org/project/devel" rel="nofollow">devel</a> module I have looked at the SQL that is created by views.  It appears that for contributions the receive date is converted from unixtime which gives <code>NULL</code>.  As a result the query returns no results.  If you remove the <code>FROM_UNIXTIME()</code> and <code>ADDTIME()</code> functions from the query, it works as expected.</p>
  
  <p>This is the SQL code for the activity view, which works fine:</p>
  
  <pre><code>SELECT civicrm_activity.activity_type_id AS civicrm_activity_activity_type_id, civicrm_value_publication_details_5.type_of_publication_8 AS civicrm_value_publication_details_5_type_of_publication_8, SUM(civicrm_value_publication_details_5.publication_price_9) AS civicrm_value_publication_details_5_publication_price_9, COUNT(civicrm_activity.id) AS id
  FROM civicrm_activity civicrm_activity
  LEFT JOIN civicrm_value_publication_details_5 civicrm_value_publication_details_5 ON civicrm_activity.id = civicrm_value_publication_details_5.entity_id
  WHERE (( (DATE_FORMAT(civicrm_activity.activity_date_time, '%Y-%m') &gt;= :civicrm_activity_date_filter AND DATE_FORMAT(civicrm_activity.activity_date_time, '%Y-%m') &lt;= :civicrm_activity_date_filter1) )) 
  GROUP BY civicrm_activity_activity_type_id, civicrm_value_publication_details_5_type_of_publication_8 LIMIT 10 OFFSET 0
  </code></pre>
  
  <p>This is the SQL code for the contributions view, which doesn't:</p>
  
  <pre><code>SELECT civicrm_contribution.id AS id, civicrm_contribution.receive_date AS civicrm_contribution_receive_date, civicrm_contribution.total_amount AS civicrm_contribution_total_amount 
  FROM civicrm_contribution civicrm_contribution 
  LEFT JOIN civicrm_contact civicrm_contact ON civicrm_contribution.contact_id = civicrm_contact.id 
  WHERE (( (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(civicrm_contribution.receipt_date), SEC_TO_TIME(3600)), '%Y-%m') = :civicrm_contact_date_filter OR DATE_FORMAT(ADDTIME(FROM_UNIXTIME(civicrm_contribution.receive_date), SEC_TO_TIME(3600)), '%Y-%m') = :civicrm_contact_date_filter1) ))
  LIMIT 10 OFFSET 0
  </code></pre>
  
  <p>I'm not sure if this is an error with the civicrm_entity module or with CiviCRM.  Can anyone help?</p>
  
Tags: <drupal><drupal-views><civicrm-entity>
Title: Unable to filter contributions by date in Drupal with views and civicrm_entity
ThreadText: # Unable to filter contributions by date in Drupal with views and civicrm_entity
  
  (Asked by: username_0 on 2015-09-17)
  
  I am trying to use Drupal Views to create a report of contributions. I would like the user to be able to filter the contributions by financial type and by date.
  
  To allow the user filter by date using a simple drop down I have installed the [civicrm\_entity](https://www.drupal.org/project/civicrm_entity) module. I have previously used this module to create views for activities and events and was able to filter these by date. However this doesn't work with contributions for some reason.
  
  Using the [devel](https://www.drupal.org/project/devel) module I have looked at the SQL that is created by views. It appears that for contributions the receive date is converted from unixtime which gives `NULL`. As a result the query returns no results. If you remove the `FROM_UNIXTIME()` and `ADDTIME()` functions from the query, it works as expected.
  
  This is the SQL code for the activity view, which works fine:
  
  ```
  SELECT civicrm_activity.activity_type_id AS civicrm_activity_activity_type_id, civicrm_value_publication_details_5.type_of_publication_8 AS civicrm_value_publication_details_5_type_of_publication_8, SUM(civicrm_value_publication_details_5.publication_price_9) AS civicrm_value_publication_details_5_publication_price_9, COUNT(civicrm_activity.id) AS id
  FROM civicrm_activity civicrm_activity
  LEFT JOIN civicrm_value_publication_details_5 civicrm_value_publication_details_5 ON civicrm_activity.id = civicrm_value_publication_details_5.entity_id
  WHERE (( (DATE_FORMAT(civicrm_activity.activity_date_time, '%Y-%m') >= :civicrm_activity_date_filter AND DATE_FORMAT(civicrm_activity.activity_date_time, '%Y-%m') <= :civicrm_activity_date_filter1) )) 
  GROUP BY civicrm_activity_activity_type_id, civicrm_value_publication_details_5_type_of_publication_8 LIMIT 10 OFFSET 0
  
  ```
  
  This is the SQL code for the contributions view, which doesn't:
  
  ```
  SELECT civicrm_contribution.id AS id, civicrm_contribution.receive_date AS civicrm_contribution_receive_date, civicrm_contribution.total_amount AS civicrm_contribution_total_amount 
  FROM civicrm_contribution civicrm_contribution 
  LEFT JOIN civicrm_contact civicrm_contact ON civicrm_contribution.contact_id = civicrm_contact.id 
  WHERE (( (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(civicrm_contribution.receipt_date), SEC_TO_TIME(3600)), '%Y-%m') = :civicrm_contact_date_filter OR DATE_FORMAT(ADDTIME(FROM_UNIXTIME(civicrm_contribution.receive_date), SEC_TO_TIME(3600)), '%Y-%m') = :civicrm_contact_date_filter1) ))
  LIMIT 10 OFFSET 0
  
  ```
  
  I'm not sure if this is an error with the civicrm\_entity module or with CiviCRM. Can anyone help?
  
  **Tags:** drupal, drupal-views, civicrm-entity
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2017-04-18. Score: 1 | Accepted answer
  
  This is working in beta6 of CiviCRM entity...probably before, can't remember exactly when I fixed this.
  
  Relevant D.O issue <https://www.drupal.org/node/2570319>
  
  > **Comments:**
  > **username_0**: That's great. Thanks for providing an update on this.  
  > **username_2**: Interestingly, using 7.x-2.1, the problem appears for activities - i.e. the activity date time is getting mangled with FROM_UNIXTIME(civicrm_activity.activity_date_time). It was okay on 7.x-2.0-beta11  
  > **username_1**: Can you make an issue at the d.o project page issue queue? That will help me remember  
  
  ---
  
  