Date filter in views not functioning properly

Using today's code everything works fine. I've made a number of commits today, some to the filter code, so try again with the latest -dev version.

just grabbed today's dev (jul 3rd) and getting this error which i think is related to the 'now' problem described here.

* user warning: Unknown column 'now' in 'where clause' query: SELECT [...] WHERE (EXTRACT(YEAR FROM(ADDTIME(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-21600)))) = now) AND (EXTRACT(MONTH FROM(ADDTIME(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-21600)))) = now) AND (EXTRACT(DAY FROM(ADDTIME(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-21600)))) = now) LIMIT 0, 10 in C:\wamp\www\drupal-5.7\includes\database.mysql.inc on line 172.

i'm guessing the 'now' in the query should have been replaced with some appropriate portion of today's date. changing _date_views_filter_handler in date_views.inc, line 128:

if (empty($filter['value']) && !empty($filter['options'])) {

to

if ($filter['value'] == 'now' || (empty($filter['value']) && !empty($filter['options']))) {

gets rid of the error but i don't know that it's the correct thing to do...

--meg

Whow! How did this one get past me :) 'now' should definitely not make it into the query. #4 may be the right fix, I'll have to investigate.

#4 I can't replicate your problem with the latest code, can you tell me what kind of date field you're using and past an export of your view?

I have a similar problem with the latest 5.x-2.x-dev tarball (2008/07/06):

I'm using Timeline module with CCK historical dates From/To. Timeline is failing to show any data (worked OK in Date 5.x-2.0-rc).

Unknown column 'now' in 'where clause' query: SELECT node.nid, node.created AS node_created_created, node_data_field_historical_date.field_historical_date_value AS node_data_field_historical_date_field_historical_date_value, node_data_field_historical_date.field_historical_date_value2 AS node_data_field_historical_date_field_historical_date_value2 FROM node node LEFT JOIN content_type_person node_data_field_line ON node.vid = node_data_field_line.vid LEFT JOIN content_field_historical_date node_data_field_historical_date ON node.vid = node_data_field_historical_date.vid WHERE (node.status = '1') AND (node_data_field_line.field_line_value IN ('Ashforth','Jacklin')) AND (EXTRACT(YEAR FROM(ADDTIME(STR_TO_DATE(node_data_field_historical_date.field_historical_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(3600)))) < now) ORDER BY node_created_created ASC in /****/familyhistoryjournal/cms/includes/database.mysql.inc on line 172.

I should add that the dates showing in Content type > CCK > Historical Date field are offset by +12:00 hours from the default Date&Time zone settings (in my case Europe/London). This seems to be offsetting all my historical dates by 1 year (I use a granularity of 'Year').

#7 I can't get a query that looks like that in the latest dev version, paste an export of your view here so I can see how it is set up.

View export:

$view = new stdClass();
$view->name = 'timeline_all';
$view->description = 'Jacklin / Ashforth timeline';
$view->access = array (
);
$view->view_args_php = '';
$view->page = TRUE;
$view->page_title = 'Jacklin / Ashforth timeline';
$view->page_header = '';
$view->page_header_format = '1';
$view->page_footer = 'Drag timeline forward or backward to see more content.

View my JACKLIN timeline

View my ASHFORTH timeline';
$view->page_footer_format = '3';
$view->page_empty = '';
$view->page_empty_format = '1';
$view->page_type = 'timeline_horizontal';
$view->url = 'timeline/all';
$view->use_pager = FALSE;
$view->nodes_per_page = '0';
$view->sort = array (
array (
'tablename' => 'node',
'field' => 'created',
'sortorder' => 'ASC',
'options' => 'normal',
),
);
$view->argument = array (
array (
'type' => 'content: field_historical_date',
'argdefault' => '2',
'title' => '',
'options' => 'year',
'wildcard' => '',
'wildcard_substitution' => '',
),
array (
'type' => 'content: to|field_historical_date',
'argdefault' => '2',
'title' => '',
'options' => 'year',
'wildcard' => '',
'wildcard_substitution' => '',
),
);
$view->field = array (
array (
'tablename' => 'node_data_field_historical_date',
'field' => 'field_historical_date_value',
'label' => '',
'handler' => 'content_views_field_handler_group',
'options' => 'default',
),
);
$view->filter = array (
array (
'tablename' => 'node',
'field' => 'status',
'operator' => '=',
'options' => '',
'value' => '1',
),
array (
'tablename' => 'node_data_field_line',
'field' => 'field_line_value_default',
'operator' => 'OR',
'options' => '',
'value' => array (
0 => 'Ashforth',
1 => 'Jacklin',
),
),
array (
'tablename' => 'node_data_field_historical_date',
'field' => 'field_historical_date_value_year',
'operator' => '<',
'options' => '',
'value' => 'now',
),
);
$view->exposed_filter = array (
);
$view->requires = array(node, node_data_field_historical_date, node_data_field_line);
$views[$view->name] = $view;

Edit your view and try again. I think the previous value was cached by views.

I mean edit and save it, to flush the cache. Or wipe out the cache by emptying cache_views.

View export #2 (flushed cache):

$view = new stdClass();
$view->name = 'timeline_all';
$view->description = 'Jacklin / Ashforth timeline';
$view->access = array (
);
$view->view_args_php = '';
$view->page = TRUE;
$view->page_title = 'Jacklin / Ashforth timeline';
$view->page_header = '';
$view->page_header_format = '1';
$view->page_footer = 'Drag timeline forward or backward to see more content.

View my JACKLIN timeline

View my ASHFORTH timeline';
$view->page_footer_format = '3';
$view->page_empty = '';
$view->page_empty_format = '1';
$view->page_type = 'timeline_horizontal';
$view->url = 'timeline/all';
$view->use_pager = FALSE;
$view->nodes_per_page = '0';
$view->sort = array (
array (
'tablename' => 'node',
'field' => 'created',
'sortorder' => 'ASC',
'options' => 'normal',
),
);
$view->argument = array (
array (
'type' => 'content: field_historical_date',
'argdefault' => '2',
'title' => '',
'options' => 'year',
'wildcard' => '',
'wildcard_substitution' => '',
),
array (
'type' => 'content: to|field_historical_date',
'argdefault' => '2',
'title' => '',
'options' => 'year',
'wildcard' => '',
'wildcard_substitution' => '',
),
);
$view->field = array (
array (
'tablename' => 'node_data_field_historical_date',
'field' => 'field_historical_date_value',
'label' => '',
'handler' => 'content_views_field_handler_group',
'options' => 'default',
),
);
$view->filter = array (
array (
'tablename' => 'node',
'field' => 'status',
'operator' => '=',
'options' => '',
'value' => '1',
),
array (
'tablename' => 'node_data_field_line',
'field' => 'field_line_value_default',
'operator' => 'OR',
'options' => '',
'value' => array (
0 => 'Ashforth',
1 => 'Jacklin',
),
),
array (
'tablename' => 'node_data_field_historical_date',
'field' => 'field_historical_date_value_year',
'operator' => '<',
'options' => '',
'value' => 'now',
),
);
$view->exposed_filter = array (
);
$view->requires = array(node, node_data_field_historical_date, node_data_field_line);
$views[$view->name] = $view;

What does your query look like now? How did you flush the cache?

Sorry for the delay, having a lot of trouble accessing drupal.org

I used the Views 'clear views cache' tool and carried out an edit and save but PHP still as in #7 returns the same error and Timeline fails to return any data.

I just noticed the arguments in your date. You have two different date arguments plus a date filter. The date filter by itself works fine. Try removing the arguments to be sure. This is an issue about the filter, so I really do think that is fixed. Plus I don't know why you would have all those date filters and arguments - the filter alone should be sufficient to make this work in the timeline. I don't think the timeline ever uses the arguments, so they're not helping here anyway.

I'll have to double check the date arguments and I'll double check my assumption about the timeline.

KarenS

I have spotted the problem:
In Views Filters,
"Date: historical date - Year (field_historical_date)
Filter by year. Set a default date and time in the Value. To default to the current time instead of a fixed time, leave the Value empty and enter 'now' in the Option. You may also use something like 'now +1 day' to default to one day from the current time."

'now' is entered in 'Value' rather than in 'Option' as shown in screen shot. The screen shot is taken from the Timeline example which I cloned. Now this is curious since it has worked with all previous versions of the Date module. Something must have changed but I'm not sure what. Will investigate further. Meanwhile I have have edited my timelines and they all work.

Many thanks for all your time and hard work, much appreciated.

Aha! Well I can add a test for that condition and/or a validation step to keep that out of there. Yes, the way that 'value' and 'option' work has changed because the Date Popup widget works differently than the old jscalendar widget worked.

I confirmed you can't get the wrong value into that field if you edit the form, so the only thing to test for is if something like 'now' is still in the date field from an earlier version of the module. That should now kick out.

Was going to report the same thing with the 5.2-dev version of date & calendar.
I have the latest dev version (today) and still see the issue. I'll try updating the module again.

Seems this issue ( http://drupal.org/node/277420 ) and these others are the same:
http://drupal.org/node/244356
http://drupal.org/node/279997
http://drupal.org/node/151325

My issue text:

I created a view with a block to display upcoming calendar items with a table view.
Just listing the items works fine, but that shows events in the past as well.

If I add a filter though to only show dates greater than or equal to 'now' like the instructions say, nothing appears, even though there are nodes with dates in the near future.

I can create three separate filters for day/year/month and that works, but of course it won't near the end of the month or end of the year, since all the filters are "and" conditions - Aug 1, 2008 would be less than July 8, 2008.

I think I found part of the problem.

class date_sql_handler->views_formats can't handle a granularity of "minute" (or "second" or "seconds" or whatever it is).
And since pretty much everyone using calendars for meetings and so forth needs the minute
level of granularity (not seconds or days), the format returned is blank.

I added a 'minute' granularity to date_api_sql.inc to the views_formats function:

      case('minute'):
        $formats['display'] = 'F j Y - H:i';
        $formats['sql'] = 'Y-m-d\TH:i';
        break;

But that still didn't fix it (both with and without the colon between the hour and minutes). Now though instead of nothing showing, everything shows in my view, even past events.

Here is the part of the sql command generated: (again the granularity is 'minute')

AND (DATE_FORMAT(STR_TO_DATE(node_data_field_eventdate.field_eventdate_value, '%Y-%m-%%dT%T'), '%Y-%m-%d\T%H:%i') >= '2008-07-08T23:45')

@dugh, those other issues are for different versions of the module. The views code is completely different in each of these versions, so the problem, and the fix, is different.

But you may have found something important that I missed in those formats, which started out being used in one way and got expanded later. I'll investigate.

@dugh Don't you need to use double quotes in your code for the escaped character to work properly? I might be completely wrong here..

Replace this:

$formats['sql'] = 'Y-m-d\TH:i';

with this:

$formats['sql'] = "Y-m-d\TH:i";

Once again, sorry if I'm being totally wrong.
St.

dugh, that was a good catch (and you don't need the double-quotes as this is being used in the current code.) I just committed a fix for this that produces the right results for me. The query it produces is the right query. If you're using a non-exposed filter, Views caches the query, so when you make changes like this you may need to clear the Views cache or just pull up the view and save it again to clear the cache.

The changes I made don't completely match your patch, so you'll want to pick up the latest code to test this.

If the query looks right, it should be displaying the right results. If you have the right query and it's *not* showing the right results, post back here.

It's still not working for me, with or without timezone handling.

without timezone handling:

SELECT node.nid, node_data_field_eventdate.field_eventdate_value AS node_data_field_eventdate_field_eventdate_value, node.title AS node_title, node.changed AS node_changed, node_data_field_eventdate.field_eventdate_value2 AS node_data_field_eventdate_field_eventdate_value2, node_data_field_eventdate.field_eventdate_rrule AS node_data_field_eventdate_field_eventdate_rrule FROM {node} node LEFT JOIN {content_field_eventdate} node_data_field_eventdate ON node.vid = node_data_field_eventdate.vid WHERE (node.status = '1') AND (node.type IN ('calendar')) AND (DATE_FORMAT(STR_TO_DATE(node_data_field_eventdate.field_eventdate_value, '%Y-%m-%%dT%T'), '%Y-%m-%d\T%H:%i') >= '2008-07-09T10:26') ORDER BY node_data_field_eventdate_field_eventdate_value ASC

with timezone handling

SELECT node.nid, node_data_field_eventdate.field_eventdate_value AS node_data_field_eventdate_field_eventdate_value, node.title AS node_title, node.changed AS node_changed, node_data_field_eventdate.field_eventdate_value2 AS node_data_field_eventdate_field_eventdate_value2, node_data_field_eventdate.field_eventdate_rrule AS node_data_field_eventdate_field_eventdate_rrule FROM {node} node LEFT JOIN {content_field_eventdate} node_data_field_eventdate ON node.vid = node_data_field_eventdate.vid WHERE (node.status = '1') AND (node.type IN ('calendar')) AND (DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_eventdate.field_eventdate_value, '%Y-%m-%%dT%T'), SEC_TO_TIME(-21600)), '%Y-%m-%d\T%H:%i') >= '2008-07-09T10:22') ORDER BY node_data_field_eventdate_field_eventdate_value ASC

Sorry I don't know what the right query is.

I'm experiencing a similar issue to this, and to #257827: Filtering for nodes of a particular day. I'm trying to display a window of events using filters for a start time greater than now -1 hour and an end time less than now +8 hours. But the query isn't reading the time part of the date, so I get no results.

Here are the relevant parts of original query.

SELECT node.nid ... WHERE ... (node_data_field_start_time.field_start_time_value > '2008-07-08 11:36:56') AND (node_data_field_end_time.field_end_time_value < '2008-07-08 20:36:56')

When I remove either of the time limitations, I can see that the remaining limitation is being read as midnight (2008-07-08T00:00:00), not the actual times I've set. So my original query fails because it's looking for events that start after and end before the exact same time.

If I insert a 'T' between the date and time, MySQL interprets the times correctly and I get valid results.

SELECT node.nid ... WHERE ... (node_data_field_end_time.field_end_time_value > '2008-07-08T11:36:56') AND (node_data_field_end_time.field_end_time_value < '2008-07-08T20:36:56')

I hope this sheds some light on the problem.

I looked in the mysql table and the dates are stored with seconds even if granularity is 'minute'.

vid 	delta 	nid 	field_eventdate_value 	field_eventdate_value2 	field_eventdate_rrule
591 	0 	328 	2008-07-05T13:55:00 	2008-07-05T19:55:00

However, forcing granularity to 'second' in date_views.inc line 192 still didn't fix it.
I'm not getting what date_api_sql.inc's date_sql_handler->sql_field function is doing.
Why for example there are 2 percentage signs in front of the day for example (changing to one didn't fix it either):

$field = "STR_TO_DATE($field, '%Y-%m-%%dT%T')";

I stuck in a msg_r and the value returned is:

STR_TO_DATE(node_data_field_eventdate.field_eventdate_value, '%Y-%m-***SQLD***T%T')

Today's snapshot is missing the commits I made today, so wait until tomorrow or use the version in cvs. I found some more fixes for the PHP4 wrapper. I'm still working through this to see if I caught everything, so probably just better to wait until tomorrow either way.

The crux of this issue are the PHP4 problems so I'm re-tagging the issue as a PHP4 issue.

As to what the right query will look like, assuming you're using an ISO date and MYSQL, the correct query will include something like:

For a field that needs no timezone conversion:
STR_TO_DATE(node_data_field_my_date.field_my_date_value)

(which converts the ISO date to something the database understands is a date)

For a field that needs timezone conversion:
ADD_TIME(STR_TO_DATE(node_data_field_my_date.field_my_date_value),  SEC_TO_TIME(-14400))

(which adjusts the converted date by the number of seconds provided)

If you're using a database that can do timezone conversions, instead of using ADD_TIME(... SEC_TO_TIME()) we use CONVERT_TZ(..., 'UTC', 'America/Chicago') because that is actually faster and more accurate when the database supports it.

I try to avoid timezone conversion where I can, so, for instance if I'm comparing a complete date in local time to a complete date in the database, I can convert my comparison date to UTC and compare that to the raw database value and avoid doing a timezone conversion. That works fine unless PHP gets it wrong (which has been one of the PHP4 problems).

If you're asking for a YEAR, MONTH, DAY, or if you are comparing to anything other that a completely formatted date, the above code will be wrapped again, to pull out the relevant value to match the value we're comparing to using either DATE_FORMAT() or EXTRACT FROM(), like the following (where '...' is the previously created field value). When we use these, we have to do timezone conversion on the database value because without doing a timezone conversion it might be extracting the wrong 'MONTH' or 'DAY'.

DATE_FORMAT(..., '%Y')

or

EXTRACT YEAR FROM(...)

Then you start to construct the comparison value part of the query. If you're comparing to 'now', we use NOW() which will be computed in UTC (since views already set the database to use UTC and it's the only 'safe' value to use). We may have to adjust NOW() to the local timezone with ADD_TIME(), depending on what timezone was used for the stored date in the database. So sometimes you see NOW() and sometimes you see ADD_TIME(NOW(), SEC_TO_TIME(-14400)).

If extracting a YEAR, MONTH, or DAY, the comparison is the number of the year month or day.

If comparing to a full date, the comparison may be the full date (in datetime format of YYYY-MM-DD HH:MM:SS), if we have one, or might be a partial formatted date like YYYY-MM-DD. If partial, the format used in DATE_FORMAT() on the field value must be the same as the format used in the comparison value.

All the logic of which value to use where is consolidated in a date_sql_handler() class so that it will be consistently applied for all queries, both in Date and in Calendar. When the wrong query gets created, it's generally because the wrong information was sent to the class.

PHP4 problems enter in here if we compute any of these values incorrectly in PHP4 or if PHP4 formats the comparison info incorrectly, especially if that causes the program to pass the wrong info to the date handler. And all the views (both Date views and Calendar views) keep track of the minimum and maximum date for the current view, and PHP4 has been getting that just far enough wrong (usually the timezone offset) that the view results don't match the date range of the view.

Anyway, I hope that I'm zeroing in on this now. I'll do some more testing and commit whatever else I find tonight, then let's give it another try tomorrow.

subscribe (came from http://drupal.org/node/151325)

Thanks for your help. I'll try your newer code.

I did find that mysql didn't like this part apparently, which has nothing to do with the date:

FROM {node} node LEFT JOIN {content_field_eventdate} node_data_field_eventdate ON node.vid = node_data_field_eventdate.vid

It gives a 1064 error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '} node LEFT JOIN {content_field_eventdate} node_data_field_eventdate ON node.vid' at line 1 

I'm using mysql 5.

If I changed the query to something like this instead, mysql only showed the future dates which is what it was supposed to do: (the full query I posted earlier)

FROM node as node LEFT JOIN content_field_eventdate as node_data_field_eventdate ON node.vid = node_data_field_eventdate.vid

So that seems more like a views issue perhaps, I don't know.

#28 - Don't confuse this issue with a question about how Date Repeat stores its values, that's completely different and needs a separate issue. The double %% signs and things like {} are in the query to goes through Drupal's db_query() function, that's not the code that goes directly to MYSQL. MYSQL will choke for sure if you try to give it those values. The odd things in the query are to make sure %d doesn't get stripped out of the final SQL by Drupal. That part is working correctly by the time it gets through Drupal's functions and is not an issue here.

I'm pretty sure I've got the rest of this solved and I'll be making commits shortly. In addition to the PHP4 problem there was a problem if you used a plain textfield as a date selector instead of using the Date Popup selector (which was what I was testing). I noticed in the screen shot in #26 that Date Popup wasn't being used which is what clued me in to that problem.

Don't try to use the code until tomorrow. Let me get everything committed and let the tarball pick up the changes and hopefully we'll have all (or at least most) of these problems resolved.

After your latest update it works perfectly for me. Thanks.

Well on further testing, only events in the next month or later are showing in a 'future events' page.
An august 1st event shows, for example, whereas a July 31 one does not.

With the new update, I'm still experiencing some of the filtering problems I described in http://drupal.org/node/280429 (marked as a duplicate of this one). Specifically, issue 1 remains the same: Setting exposed date filters as required doesn't seem to work correctly. Issue 2 is partially fixed, in that setting the default values to "now" no longer breaks everything, but it also initially results in a listing of all nodes that have the Date field filled in, which is definitely wrong. Happily, the third issue is completely fixed (and that was the one that bugged me the most). Nicely done!

same problem, after upgrading to Date rc2, all my views with list of nodes (similar events, with from and to dates) are totally unsorted (maybe sorted but in weird wrong time).

I use filter Greater than equal and option is "now + 1hour"

the other views are similar, now + 1 day, etc.

igorik
http://www.somvprahe.sk

return to old date version - // $Id: date_api.module,v 1.44.2.27 2008/06/18 11:14:06 karens Exp $
it temporarily solved. But I was so happy that we have date rc2 already... ;-)

igorik

igorik - the date filter does not do or change sorting at all, sorting is done by whatever you put in the view as the 'sort' value. If your view is unsorted, you must not have added a sort to it.

Hi Karen, I did no changes of sorting my views.

I just upgraded on date rc2, and it makes my listing unssorted(wrong sorted).
return to previous version of Date module (I wrote version in my previous post) it make works correctly. So there was no changes in views, just with upgraded of date module.

Igorik

my sorting (sort criteria) is based on CCK date field in node, and filter is based on that field: greatr or equal: and option is now - 1 hour.
But as I told, I did no changes with views, just update Date module to rc2

i have a similar problem after upgrading to date rc2 from rc1

i have several views on my site which use a single date filter with setting greater than now -1 month
worked fine in rc1 but after upgrading to rc2 all the views show zero result
if i change it to greater than or equal now -1 month then it'll show all nodes matching other filters, the date filter is not functioning
if i change from now -1 month to a static date value that it works fine

i have only upgraded date module and no change on the views

The date filter does not do any sorting. If there's a problem with sorting, it's something else. Open a new issue about a sorting problem and paste in an export of your view, an export of the date field (use Content Copy to export that), and tell me what version of PHP you're using.

I've updated the module, but I still get no results if there are less than 24 hours between my two date filters. If you would like me to post this as a separate issue, please let me know.

SELECT node.nid ...  WHERE ... (node_data_field_end_time.field_end_time_value <= '2008-07-11 22:59:57') AND (node_data_field_start_time.field_start_time_value > '2008-07-11 14:29:57') 

#42 is a different issue about using two different filters together, so it needs its own issue. And I need to see the whole query, don't cut parts of it out. And I need an export of your view and and export of the date field you use in the query, and what version of PHP you're using. I need all that info so I can try to reproduce your bug. If I don't have it, I'm just shooting in the dark.

I create a new bug about wrong sorting regard KarenS request.
It is here,
http://drupal.org/node/281537
please next discussion and info about wrong sorting after update to rc2 add there.
Igorik

Like BenKewell, if I replace "now" with a static date, everything works perfectly. So using the devel module, here is the difference between the queries:

using > 'now': (only shows events in next month or greater)

AND (DATE_FORMAT(STR_TO_DATE(node_data_field_eventdatetime.field_eventdatetime_value, '%Y-%m-%%dT%T'), '%Y-%m-%d\T%H:%i') > '2008-07-11T13:23') ORDER BY node_data_field_eventdatetime_field_eventdatetime_value ASC

using > '2008-07-11 13:18': (works)

AND (STR_TO_DATE(node_data_field_eventdatetime.field_eventdatetime_value, '%Y-%m-%%dT%T') > '2008-07-11 13:18:00') ORDER BY node_data_field_eventdatetime_field_eventdatetime_value ASC

It could be that "T" in the 'now' date, or the escaped T (\T) in the date_format string, I'm not sure.
The dates in the database look like so: 2008-09-15T13:50:00
I have php 5.2.6 and mysql 5.0.45.

The 'T' is not the problem, it's the double %% in front of the 'd'. The second query works better than the first one because it's not trying to do DATE_FORMAT() on the result of the bad value. If you have phpMyAdmin or something like that you can test this. Paste in the queries as they are into the 'SQL' box and see what you get, then fix both queries by getting rid of the double '%%' and use a single '%' instead and see what you get. If you get the wrong results without that fix and the right results with it, that's the issue.

Thanks for all your help, if I change %%d to %d the query works fine in mysql, but I'm not sure about the right place to fix that in the module.

I changed the last function in date_api.module to return return array('***SQLD***' => '%d'); instead of %%d and that fixed the query string (according to the devel module), but the view showed no results at all so that's obviously not the right way to fix it :)

KarenS, per your request I've posted a new issue -- #281570: Date filter not reading times. However, I don't believe the problem is due to there being two filters. From my testing, it appears to be a time processing problem, so I've named the issue accordingly.

I found part of the problem but not a fix. It's still related to the Views bug which is fixed in the current -dev version of Views but broken in the latest official release. I added in a method to try to keep Views from replacing '%%d' with '%d' and then '%d' with '0' in the final query, but my fix isn't enough of a fix. The reason this is only a problem with filters is that views with filters sometimes get cached, and what is cached is the query string itself. But when queries are cached, they sometimes get run in realtime and sometimes get pulled from the cache. I was trying to do a substitution to get the value fixed before the query went into the cache, and I accomplished that (or mostly accomplished that, I found one more small fix there), but when the query is pulled out of the cache it gets garbled again at that point. If I fix it so the query coming out of the cache will work right, it won't go into the cache correctly.

And because of the way Views is structured I don't have many (or any) places where I can step in to keep it from doing the wrong thing. None of this is a problem if you're running the latest -dev version of Views, only with the official release (which is what most people are using).

Until I can find a reliable way to make sure the query is right no matter whether or not it goes in or comes out of the cache, this will be broken. I'll find something, but it's not easy.

Thanks again for your help. I installed views from cvs and it is working fine.

For those who want the cvs version of views, see these instructions (the first comment):
http://drupal.org/node/120511

Or if i can get these other issues fixed:
http://drupal.org/node/183191
http://drupal.org/node/239594
I'll upload my copy of the views module here: http://drupal.org/node/281811

hm, I tried to replace my views vith views 5.x-1.x-dev http://drupal.org/node/101135, clear views cache, but then I got wrong sorted list of events again.
so it doesn't work for me. If I am doing something wrong please let me know.

this is my view:

  $view = new stdClass();
  $view->name = 'blok_vseobecne_akcie_bliziac_dlh';
  $view->description = 'BLOK Vseobecne akcie - Zoznam najbližších akcií DLHSIE';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = FALSE;
  $view->page_title = '';
  $view->page_header = '';
  $view->page_header_format = '3';
  $view->page_footer = '';
  $view->page_footer_format = '3';
  $view->page_empty = '';
  $view->page_empty_format = '3';
  $view->page_type = 'list';
  $view->url = 'akcie/komunitne/zoznam-najblizsie';
  $view->use_pager = TRUE;
  $view->nodes_per_page = '30';
  $view->block = TRUE;
  $view->block_title = 'Všeobecné akcie - najbližšie';
  $view->block_header = '';
  $view->block_header_format = '3';
  $view->block_footer = 'Ukáž všetky: <a href="http://www.somvprahe.sk/akcie/komunitne/zoznam-najblizsie">zoznam</a>, <a href="http://www.somvprahe.sk/kalendar/akcie/komunitne">kalendár</a>, <a href="http://www.somvprahe.sk/timeline/akcie/komunitne">časová os</a>';
  $view->block_footer_format = '3';
  $view->block_empty = '';
  $view->block_empty_format = '3';
  $view->block_type = 'list';
  $view->nodes_per_block = '6';
  $view->block_more = FALSE;
  $view->block_use_page_header = FALSE;
  $view->block_use_page_footer = FALSE;
  $view->block_use_page_empty = FALSE;
  $view->sort = array (
    array (
      'tablename' => 'node_data_field_odkedy',
      'field' => 'field_odkedy_value',
      'sortorder' => 'ASC',
      'options' => '',
    ),
  );
  $view->argument = array (
  );
  $view->field = array (
    array (
      'tablename' => 'node',
      'field' => 'title',
      'label' => '',
      'handler' => 'views_handler_field_nodelink_with_mark',
      'options' => 'link',
    ),
    array (
      'tablename' => 'node_data_field_odkedy',
      'field' => 'field_odkedy_value',
      'label' => '',
      'handler' => 'content_views_field_handler_ungroup',
      'options' => 'timestamp',
    ),
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'status',
      'operator' => '=',
      'options' => '',
      'value' => '1',
    ),
    array (
      'tablename' => 'node',
      'field' => 'type',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
  0 => 'vseobecna_akcia',
),
    ),
    array (
      'tablename' => 'node_data_field_odkedy',
      'field' => 'field_odkedy_value_default',
      'operator' => '>=',
      'options' => 'now -1 hour',
      'value' => '',
    ),
  );
  $view->exposed_filter = array (
  );
  $view->requires = array(node_data_field_odkedy, node);
  $views[$view->name] = $view;

thanks
Igorik
http://www.somvprahe.sk

*PLEASE* don't add sorting problems to this issue, it's complicated enough already. We have a separate issue about sorting.

I finally got this worked out. I was able to reproduce all the previous problems, including the odd place where the query was filtering for the year only instead of the whole date that seemed to be a another PHP4 problem, and I found a fix for everything that seems to work whatever version of Views you're using, and in both PHP4 and PHP5.

I just committed these fixes, so they won't show up in the tarball for another 12 hours or so, but I am pretty confident this issue is actually fixed now.

But I still can't filter by 'greater than now', or 'greater or equal than now', etc.

Drupal 5.7
Calendar 5.x-2.x-dev (2008-lp.-10)
Content Construction Kit (CCK) 5.x-1.x-dev (2008-lp.-12)
Date 5.x-2.x-dev (2008-lp.-15)
Views 5.x-1.x-dev (2008-maj-11)
PHP 5.2.0-8+etch11

('lp' means July)

The view:

  $view = new stdClass();
  $view->name = 'Imprezy_Najblizsze_z_Foto';
  $view->description = '';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = FALSE;
  $view->page_title = '';
  $view->page_header = '';
  $view->page_header_format = '1';
  $view->page_footer = '';
  $view->page_footer_format = '1';
  $view->page_empty = '';
  $view->page_empty_format = '1';
  $view->page_type = 'teaser';
  $view->url = 'miasto';
  $view->use_pager = FALSE;
  $view->nodes_per_page = '3';
  $view->block = TRUE;
  $view->block_title = 'Polecamy imprezę';
  $view->block_header = '';
  $view->block_header_format = '1';
  $view->block_footer = '';
  $view->block_footer_format = '1';
  $view->block_empty = '';
  $view->block_empty_format = '1';
  $view->block_type = 'teaser';
  $view->nodes_per_block = '1';
  $view->block_more = FALSE;
  $view->block_use_page_header = FALSE;
  $view->block_use_page_footer = FALSE;
  $view->block_use_page_empty = FALSE;
  $view->sort = array (
    array (
      'tablename' => 'node',
      'field' => 'random',
      'sortorder' => 'ASC',
      'options' => '',
    ),
  );
  $view->argument = array (
  );
  $view->field = array (
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'status',
      'operator' => '=',
      'options' => '',
      'value' => '1',
    ),
    array (
      'tablename' => 'node',
      'field' => 'type',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
  0 => 'impreza',
),
    ),
    array (
      'tablename' => 'node_data_field_foto',
      'field' => 'field_foto_fid_not null',
      'operator' => '=',
      'options' => '',
      'value' => '1',
    ),
    array (
      'tablename' => 'node_data_field_data',
      'field' => 'field_data_value_default',
      'operator' => '>=',
      'options' => 'now',
      'value' => '',
    ),
  );
  $view->exposed_filter = array (
  );
  $view->requires = array(node, node_data_field_foto, node_data_field_data);
  $views[$view->name] = $view;

This and other views using 'now' filter are blank. They show up when I delete filter... :/

Any idea? Tia...

Szy.

Now I see that I have lost my CCK date field, because of inactivating the module for a while and that's why date views are empty...

Arrrggghhh! :/

I was wrong: date field is back again, modules are updated, all is fine... except views filtered with 'now'... Huh.

Szy.

szy, just edit your view again and re-save it as 'greater than or equal to' 'now' and it should work.

Karen, Dugh: thanks, it was close: I had to:

1. remove previous CCK data field,
2. add new one,
3. re-edit all nodes with data data ;).

Now it's fine.

Thank you! :*

Szy.

Great stuff.

Does that mean that version 6 is fixed as well?

Working on version 6 now -- the version 6 code is totally different because it uses Views 2, which is totally different.

Man, this one is giving me fits as well. Trying to use this thread to learn why I cannot filter by date using 'now'. Having no success.

I have a view of 25 workshops. Created a block that will show the upcoming 3 workshops from the list. It will display workshops but I cannot get it to filter by 'today' so that past workshops do not show up in the block.
I'm using drupal6.3, Views 6.x-2.0-rc1(non-dev version) and Date 6.x-2.0-beta3 modules

I just tried using the dev version of Views with the same results.

As well, if I try to put in a date to filter by (ex: 2008/07/25) I get an "Invalid entry" error.

this one's gonna be tough I think.

Dan

@daniel-san - the D6 version is totally different because Views 2 is totally different than Views 1. This is a D5 issue. Nothing in this issue will help you with the D6 version. The D6 version is slower because of all the massive changes in Views, but is mostly working. But please don't confuse things by commenting about D6 problems on a D5 issue.

Thanks Karen. Appreciate it. I guess I'm waiting it out while the kinks get worked out.