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.

CommentFileSizeAuthor
#3 term_fields_timestamp.patch2.58 KBvalcker

Comments

valcker’s picture

Title: Date field is saved in DATETIME » Date field is saved as DATETIME
b-prod’s picture

I will take a look on term_fields_handler_field_date handler.

valcker’s picture

StatusFileSize
new2.58 KB

You 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.

nancydru’s picture

You 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.

b-prod’s picture

Version: 6.x-1.x-dev » 6.x-2.x-dev

The 2.x branch support DATE_ISO and DATETIME storage format for SQL.
Is this issue still resolved?

nancydru’s picture

After more than a year, I am no longer on that account.

b-prod’s picture

Status: Active » Closed (works as designed)