By chumoka on

Hi I'm using the Views module (6.x-2.6) and the Term fields (6.x-1.5) to create taxonomy terms with and additional date field. However, when I try to create a View for these taxonomy terms and filter based on this custom date field, it's not working properly.

If I set I only want to display those terms, that have a date field "Greater than +1 day" it displays all the terms (including those dated for 2008 for example). If I set "Less than +1 day" it wouldn't display anything, same situation with the BETWEEN command. Nothing is displayed.

Here is the SQL querry for the "Less than 1 day":

SELECT term_data.tid AS tid,
term_fields_term.popis_vystavy AS term_fields_term_popis_vystavy,
term_fields_term.tid AS term_fields_term_tid,
term_data.name AS term_data_name,
term_data.vid AS term_data_vid,
term_fields_term.vystava_od AS term_fields_term_vystava_od,
term_fields_term.vystava_do AS term_fields_term_vystava_do,
term_fields_term.vystava_mesto AS term_fields_term_vystava_mesto
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.vystava_od < ***CURRENT_TIME***+86400
ORDER BY term_fields_term_vystava_od DESC, term_data_name ASC

Anyone could help me with this? Thanks.

Edited by WorldFallz - moved to appropriate forum.

⋅ Categories: Drupal 6.x

Comments

chumoka’s picture

chumoka commented

Anyone had any similar problem?

albany’s picture

albany commented

Hope this helps..

If I'm reading it correctly... your asking for any posts that are older than now less than , all of them are older than now so all of the posts will be returned.

use

WHERE field is >= ( - 86400)

Regards

Steve

chumoka’s picture

chumoka commented

Thank for your quick advice, but that's not exactly the problem.

I'm not working with posts in this specific case. I'm working with taxonomy term, on my page these terms are basically some events. It means, there is some name, start date, end date, location, etc. Then I create a view (block), and I set, that only display those events, where the start date is from - 2 days to +10 days, for example. But then the above discribed problem comes up.

Anyway, I had a closer look at the time format in the database, and it's a bit strange. At my "events" the start date and the end date is in a format of 2010-02-05 00:00:00 (yyyy-mm-dd hh:mm:ss), but for example when I create a node than the created: field in the database is 1258818678. Do you think it could be the reason why the sql querry doesn't return the proper values? Any solution on this?

Thanks a lot again.
Gabriel

chumoka’s picture

chumoka commented

Is there anyone having had the same problem? Any idea what could cause the problem?

valcker’s picture

valcker commented

I'm having the same problem. In my case it's BETWEEN... I suppose the problem is in this:


mysql> describe term_fields_term;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| tid   | int(10) unsigned | NO   | PRI | NULL    |       | 
| test  | datetime         | YES  |     | NULL    |       | 
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

We cannot compare DATETIME field with timestamp.

valcker’s picture

valcker commented

Issue have been submitted: http://drupal.org/node/792626