We cannot use Views filtering by Date term_field because date fields are saved as DATETIME:
mysql> describe term_fields_term;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| tid | int(10) unsigned | NO | PRI | NULL | |
| test | datetime | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
Views generate next code when we try to filter results by date term_field:
SELECT term_data.tid AS tid, term_fields_term.test AS term_fields_term_test, term_fields_term.tid AS term_fields_term_tid FROM term_data term_data
LEFT JOIN term_fields_term term_fields_term ON term_data.tid = term_fields_term.tid
WHERE (term_fields_term.test >= ***CURRENT_TIME***-5270400) AND (term_fields_term.test <= ***CURRENT_TIME***+5097600)
MySQL cannot compare DATETIME field with Unix timestamp. I suppose that date should be saved in INT(11) or at least views date handler should be overloaded to convert datetime fields to Unix timestamp.
It seems that support for different date formats is planned (function term_fields_format_date) but not 100% implemented yet.
Both latest stable and dev versions are affected.
Comments
Comment #1
valcker commentedComment #2
b-prod commentedI will take a look on term_fields_handler_field_date handler.
Comment #3
valcker commentedYou can do something like shown in the patch (wrapping term date field in where clause with UNIX_TIMESTAMP(...) in the query) but I don't like it. Though it's a quick solution that works.
Comment #4
nancydruYou should focus your energy here: #476774: Allow date handler to cope with 'date', 'datetime', 'datetime_with_timezone', 'ical', 'ical_date' and 'iso' formats
Unix time stamps run out in only 27 years. It's time to stop using them.
Comment #5
b-prod commentedThe 2.x branch support DATE_ISO and DATETIME storage format for SQL.
Is this issue still resolved?
Comment #6
nancydruAfter more than a year, I am no longer on that account.
Comment #7
b-prod commented