I want to display a calendar that includes the events from this month and the next eleven months. I.e., if it's January 31, 2013, I still don't want to see events for January 1, 2014. If it's February 1, 2013, I do.

In PHP I can use the relative date format "first day of today + 1 year" to do this:

php > print date('r', strtotime('first day of today + 1 year'));
Wed, 01 Jan 2014 00:00:00 -0600
php > print date('r', strtotime('first day of today  + 1 year', strtotime('2013-04-05 12:37 -0600')));
Tue, 01 Apr 2014 00:00:00 -0500
php > print date('r', strtotime('first day of today  + 1 year', strtotime('2013-07-31 12:37 -0600')));
Tue, 01 Jul 2014 00:00:00 -0500

Unfortunately, when I try to use this in the Views filter, all my view results disappear. When I look at the SQL query, where the date should be…

(DATE_FORMAT(ADDTIME(field_data_field_date.field_date_value, SEC_TO_TIME(-21600)), '%Y-%m-%d') < '2014-01-16')

…is an empty set of quotation marks:

(DATE_FORMAT(ADDTIME(field_data_field_date.field_date_value, SEC_TO_TIME(-21600)), '%Y-%m-%d') < '')

What gives?

Comments

lanny heidbreder’s picture

lanny heidbreder commented

(When I said I want to display a "calendar", what I meant of course is that I want a simple list.)

jwilson3’s picture

jwilson3
Primary language English
Location Ecuador
commented

The problem is in your definition of what you want to show. If you're on the last day of the month, you don't want to show any results for that month, not even including events on the current day? That sounds really impossible to do without a custom formatter. im no pro at relative php dates but "first day of today" is bad english, and my guess is that php is getting confused too.

On the other hand if all you're trying to do is show everything from now() until now + 11 months, you should use two filters, joined by OR

start date <= now (with minute granularity) AND end date >= now (with minute granularity)
(to show current events, assuming you're using events that have start and end dates).

OR

start date >= today (with day granularity) AND start date <= this month + 11 months (with month granularity)

lanny heidbreder’s picture

lanny heidbreder commented

Yes, it's bad English, but it's a perfectly valid PHP relative date format. You can see in my first quote that PHP returns valid, consistent results for that string. "first day of today" means "Go to the first day of the month at the same time as (today at midnight)". Then I add a year, thereby getting the remainder of the current month and the next eleven months. But for some reason the views handler thingy doesn't like that string at all.

All that aside, though, your alternate solution is better and perfect. Thanks!