Come together with the global Drupal community in Rotterdam, 28 Sept – 1 Oct 2026. Sessions, contribution, connection, and Early Bird savings until 8 June.When creating a views filter on a CCK date field, the date module creates a SQL query that looks like this
((DATE_FORMAT(node_data_field_highwire_a_epubdate.field_highwire_a_epubdate_value, '%Y-%m-%d') >= '2011-10-28')
When this type of query is created, there is no way that the database can use indexes as it is forced to create a temporary table because of the DATE_FORMAT function. Instead, this kind of SQL should be created:
node_data_field_highwire_a_epubdate.field_highwire_a_epubdate_value >= '2011-10-28'
Something like this will use indexes and not create a temporary tables.
For us, this problem is causing queries that take 2.5 seconds to run. Removing the DATE_FORMAT function causes the same queries to run, with the same results, in 14 milliseconds.
| Comment | File | Size | Author |
|---|---|---|---|
| #4 | date_sqlformat.patch | 423 bytes | PeteR |
| #1 | date_filter.patch | 416 bytes | pwaterz |
Comments
Comment #1
pwaterz commentedhere is the patch
Comment #2
phayes commentedComment #3
phayes commentedHmm.. This appears to to break some things. I wonder if, instead of changing the format function to do nothing, we should instead change it so that views filters dont use formatting.
Comment #4
PeteR commentedThere is another issue with DATE_FORMAT function. If date is a local format the evaluation in SQL does not work correctly. Lets say we have the following query:((DATE_FORMAT(node_data_field_highwire_a_epubdate.field_highwire_a_epubdate_value, '%d.%m.%Y') >= '05.02.2012'). In this case e.g. 06.01.2010 would also pass this condition - which is non sense.
This is another reason why DATE_FORMAT should be completely excluded from the query. Suggested solution is to convert date in any local format to iso format within PHP and then simply evaluate in SQL. Here is the patch.
Comment #6
damienmckennaUnfortunately the D6 version of this module is no longer supported, but we appreciate the time you put into this. If this problem is relevant for D7 too, please reopen the issue. Thanks.