Views - Filtering Nodes by Date

Quite often you need to display a list of nodes filtered by a date. A common scenario is you'll have an "events" content type and you only want to display events that haven't already taken place.

So you've created your content type and added a date field using CCK. Your view is set up to filter on "event_date is greater than or equal to now()". This should work just fine, if in your event_date field settings, you've included hour, minute and second granularity.

In some cases though, you'll just want a date and no time - this is were you need to be careful.

In the above scenario you'd lose all nodes where event_date is today from your results - which seems strange since you've set the filter to greater than or equal to now().

If we have a date field with no hour, minute and second granularity and we enter 18/01/2008 as our date, what actually gets stored in the database is this:-
2008-01-18T00:00:00
So effectively when we view our events page over toast and coffee at 9am on the 18/01/2008 we won't see this node because now() is actually 2008-01-18 09:00:00.

By the way, the views query takes care of the "T" in the stored value in the SELECT.

So what's the solution?

On the filter we can add a delta value in seconds in the Option that get's added to now() before the comparison is made - if we enter -86400 (24 hours) our view will work as expected.