CiviCRM Community Forums (archive)

When searching against a date field, MySQL behaves differently if you wrap your search term in quotes or not:

Here's the civicrm_case table I'm using:

mysql> DESC civicrm_case;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| case_type_id | varchar(128)     | NO   |     | NULL    |                |
| subject      | varchar(128)     | YES  |     | NULL    |                |
| start_date   | date             | YES  |     | NULL    |                |
| end_date     | date             | YES  |     | NULL    |                |
| details      | text             | YES  |     | NULL    |                |
| status_id    | int(10) unsigned | NO   |     | NULL    |                |
| is_deleted   | tinyint(4)       | YES  |     | 0       |                |
+--------------+------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql>

Notice that start_date is a date field.

Here are three nearly identical queries, the first one shows incorrect results, the 2nd and third show correct results:

Incorrect (should only show dates less than Nov 30, 2011, instead shows a date in December 2011):

mysql> SELECT start_date FROM civicrm_case WHERE start_date <= 20111131235959 ORDER BY start_date DESC LIMIT 1;
+------------+
| start_date |
+------------+
| 2011-12-06 |
+------------+
1 row in set (0.00 sec)

mysql>

Wrapping in single quotes produces the correct result:

mysql> SELECT start_date FROM civicrm_case WHERE start_date <= '20111131235959' ORDER BY start_date DESC LIMIT 1;
+------------+
| start_date |
+------------+
| 2011-11-30 |
+------------+
1 row in set (0.01 sec)

mysql>

Only providing the first 8 digits also provides correct results:

mysql> SELECT start_date FROM civicrm_case WHERE start_date <= 20111131 ORDER BY start_date DESC LIMIT 1;
+------------+
| start_date |
+------------+
| 2011-11-30 |
+------------+
1 row in set (0.00 sec)

mysql>

If I convert start_date into a datetime, then it works fine without quotations:

mysql> ALTER TABLE civicrm_case CHANGE start_date start_date datetime DEFAULT NULL;
Query OK, 5064 rows affected (0.61 sec)
Records: 5064  Duplicates: 0  Warnings: 0

mysql> SELECT start_date FROM civicrm_case WHERE start_date <= 20111131235959 ORDER BY start_date DESC LIMIT 1;
+---------------------+
| start_date          |
+---------------------+
| 2011-11-30 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql>

CiviCRM code seems to use a mixture of these variations on searching for date ranges.

I discovered the problem debugging a CiviCase Demographics report that uses a date field and searches without quotes and with the full 14 digit date/time integer.

I posted the bug (and patched the code) here: http://issues.civicrm.org/jira/browse/CRM-9318

The patch takes the simplest route to fixing this particular problem (by ensuring that the search uses the 8 digit integer rather than the 14 digit integer).

However, I wonder if CiviCRM code should always put date queries in single quotes instead?

jamie

Well I've always been of the opinion that there is no such (absolute) thing as case start date (it depends on the role of the person you are talking to). So one solution is kill start_date and end_date. But I always seem to lose that argument as being too abstract.

I do understand the technical inefficiency argument (start_date is redundant summary level info which could be computed on the fly from activities but as a compromise is also stored separately for efficiency).

So, related to that I can only think of one area that might need looking at if all (case) fields are set to datetime. There is code in at least one place that knows about the field definition difference and might explicitly do something odd with the time component while trying to keep start_date and open case activity datetime in sync.

I'll look tomorrow where these places might be.