I need to create a view that shows currently ongoing exhibitions which may or may not have end dates (this is for a museum). In other words, nodes should be shown if their start date is in the past, and their end date is in the future or they have no end date.

I thought I could create a view with the following filters:

start date <= now
AND
( end date >= now OR end date == null )

But this doesn't work because nodes without an end date get filtered out. Apparently blank end dates aren't null. Anybody know how I can get this working?

Thanks

Comments

arlinsandbulte’s picture

Blank end dates are automatically populated with the same value as the start date. See #874322: To Date & All Day Date Handling & #523218: Default value for To date: Blank should stay blank for more details.

This might work, but IIRC, I don't think two date fields can be compared (can't do start date = end date in views)
( start date <= now AND start date = end date )
OR
( start date <= now AND end date >= now )

eusonic’s picture

Thanks arlinsandbulte,

I'm fairly certain Views filters can't compare two fields. This may be possible with the Views PHP module, but that's a hack I'd like to avoid.

Another idea is to use separate fields for start and end dates, then null should function as expected. However, I suspect this will make it impossible to display multi-day nodes properly with the Calendar module.

das-peter’s picture

Category: support » bug
Status: Active » Needs review
FileSize
1.71 KB
FAILED: [[SimpleTest]]: [MySQL] 5,214 pass(es), 3 fail(s), and 0 exception(s). View

I've just encountered this issue too, for me this is definitely a bug.
Because I use already the date views module I decided to extend the filter handler in a very pragmatic way.
If the filter "empty" or "not empty" is used for an end-date value the handler adds a comparison with the from-date value now.
Now the filter seems to work as expected :)

Status: Needs review » Needs work

The last submitted patch, date-views-filter-handler-end-date-fix-1635810-3.patch, failed testing.

das-peter’s picture

Looks like the failed tests are related to Migrate (Class 'Migration' not found) and not the changes I made.

das-peter’s picture

Status: Needs work » Needs review

Status: Needs review » Needs work

The last submitted patch, date-views-filter-handler-end-date-fix-1635810-3.patch, failed testing.

das-peter’s picture

Status: Needs work » Needs review

Status: Needs review » Needs work

The last submitted patch, date-views-filter-handler-end-date-fix-1635810-3.patch, failed testing.

arnested’s picture

Status: Needs work » Needs review

Status: Needs review » Needs work

The last submitted patch, date-views-filter-handler-end-date-fix-1635810-3.patch, failed testing.

das-peter’s picture

Status: Needs work » Needs review

Status: Needs review » Needs work

The last submitted patch, date-views-filter-handler-end-date-fix-1635810-3.patch, failed testing.

Renee S’s picture

Any luck on this? It's totally what I'm looking for too :)

das-peter’s picture

Status: Needs work » Needs review

Status: Needs review » Needs work

The last submitted patch, date-views-filter-handler-end-date-fix-1635810-3.patch, failed testing.

jannis’s picture

Status: Needs work » Needs review

Status: Needs review » Needs work

The last submitted patch, date-views-filter-handler-end-date-fix-1635810-3.patch, failed testing.

kong’s picture

Subscribed.

shadysamir’s picture

The patch in #3 did not pass tests of date repeat and date migrate, which I dont use in my site, so I decided to apply it regardless. Works great, but it's still a hack around the main problem: Optional empty end date should be NULL

iamEAP’s picture

Status: Needs work » Needs review

I have a hunch that the automated tests are failing regardless of the patch (e.g. current HEAD is broken for unrelated reasons).

thinkyhead’s picture

In the site I'm working on the expected behavior for the exposed date filter is that a "between" search with a blank end date should be treated as a ">=start_date" search. (Using "now" as the end date is wrong because we also want to see future items.) That makes the most sense to me for any exposed date filter that's set to "between." Setting it the same as the start date is presumptuous and semantically confusing for users. At the moment a blank end date in the form is just causing it to do no date filtering at all, which must be a bug.

(I'm using Date module 7.x-2.6)

das-peter’s picture

Could please someone confirm that the patch works? I can't imagine that the test fails have something to do with the patch. They fail because the Migrate class couldn't be enabled - but this patch has nothing to do with Migrate.
If nobody has encountered any errors with the patch and it solves the problem for you, please set this issue to RTBC.

iamEAP’s picture

Status: Needs review » Reviewed & tested by the community

Can confirm the patch works as advertised. Have been running this in prod for nearly a month.

Pipolaye’s picture

Hello I have a similar issue with dates:

I'm selling training session on a website, they can be over 1 or 2 days.

If the training session is over 2 days I would like to display: "from 23/07/2013 to 24/07/2013" and if the training session is on 1 day: "23/07/2013".

I have created 2 fields into my view: "Start date" (hidden) and "End date".

If "End date" is empty, it should display the "Start date" value.

However "End date" is never empty...

This patch didn't fix this issue.

Any idea?

Cheers

mesr01’s picture

Patch #3 worked for me. Thanks @dat-peter!

codecouleurs’s picture

But with this patch, we can't published a content just one day because start date = end date is equal to no end date...

The best way in my opinion is really not populate end date when the checkbox "Define an end date" is uncheck so we can test if there is really no end date or if we choose to set the end date same as the start date.
In association with a help text in date field configuration to say that an uncheck checkbox "Define an end date" means published all the time.

What did you think about it ?

manuelBS’s picture

+1 to #27

rhclayto’s picture

cimo75’s picture

+1 to #27 too

das-peter’s picture

Status: Reviewed & tested by the community » Needs work

Guy's if you say +1 to #27 this means "needs work". And for the maintainers of views is not really useful to have a patch that's not ready marked as RTBC ;)

Btw. I'm absolutely fine with this, but currently it's unlikely that I find some time to dig deeper and fix it.
Thus, if someone has an idea how to fix it please feel free to take over.

erinclerico’s picture

Status: Needs work » Needs review

Status: Needs review » Needs work

The last submitted patch, date-views-filter-handler-end-date-fix-1635810-3.patch, failed testing.

Anonymous’s picture

Issue summary: View changes

minor correction

Charles Belov’s picture

The issue is not so simple. There can be multiple reasons for the lack of an end date:

1. Something begins, and will end when something else is put in its place, e.g., a policy.

2. Something begins, and will end on some future date when it's done, e.g., an open-ended stage run or construction with no fixed schedule.

For items 1 and 2, if there must be something in the end date, the most future date possible would seem to be most appropriate, e.g., Dec. 31, 9999, or whatever the maximum date value is. (Sorry if I'm setting us up for a Y10K problem.) Views could already test for this.

The ideal English date display for items 1 and 2 would be: Effective [start date]

3. Something begins, and will probably end on the same day, when the work for that day is done, e.g., a public hearing, or at worst case in the wee hours after midnight early the next morning.

4. There is also the idea that something is being published, and it will always have been published, but at some point it won't be particularly interesting that it's been published, e.g., a press release that is not about something that has a definite end time. (But we can't just drop the start and end dates off, because there are press releases that do concern matters with specific end times.)

For item 3 or 4, if there must be something in the end date, having the same start and end date would seem to be appropriate, and issue #1635810 would be needed to solve it.

The ideal English date display for items 3 and 4 would be: [start date]

For completeness:

5. There is a definite interesting, typically future, start date and a definite interesting end date, both known, e.g., definitely scheduled construction.

This of course is what date entry is best set up for, as there is no ambiguity, and is already solved. Enter the start and end date.

The ideal English date display would be: [start date] - [end date] or [start date] to [end date] or [start date] through [end date]

6. The start date is in the relatively distant past. Whether or not it is known, there is no particular reason to display it, e.g., a bus route that is about to be discontinued.

In this case, it's easy to know what to put in for the end date, but not for the start date. Ideally, the start date would be the most past date it is possible to display in a Drupal date field, e.g., Jan. 1, 0001. Again, we would never want to display this date.

The ideal English date display would be: Through [end date]

That said, one could reasonably argue that item 6 is just a contrary way of stating item 1. Still, it would be nice to be able to express item 6 in case there's a use case, and to be consistent with how I'm proposing we handle end date.

I think it is too much to ask staff to have to put in Dec. 31, 9999, or put in identical times for start and end, so ideally in place of the check box Show End Date, there would instead be radio buttons:

  • Indefinite end date (default default)
  • Indefinite same-day end time
  • Indefinite start date/definite end-date
  • Definite start and end dates

where the default could be overridden by the administrator by content type.

It's a whole 'nother issue for #1 and #2 remembering to go back and put in an end date if and when it finally happens. While that's outside the scope of this discussion, use of a most-future end date would allow such cases to be easily reported to whoever is responsible for checking for the up-to-date-ness of the website.

Charles Belov’s picture

Issue summary: View changes
Charles Belov’s picture

bc’s picture

To solve the problem of filtering on start date == end date, I'm using the new views_handler_filter_fields_compare, but with the attached patch.

I added two additional fields to my view: one is the start date only and another is the end date only. These two fields can be compared using the above-mentioned filter.

The trick is that filter_fields_compare only pays attention to one SQL column per field. Start-end dates have two columns in each field, of course. So, I added a test to the filter to check if the views field option for end date was set. This is hacky. I think the views field handler in the date module is weird.

bc’s picture

FileSize
1.36 KB
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch views-filter-compare-handler-for-duple-dates-6242858-37.patch. Unable to apply patch. See the log in the details link for more information. View
Shyghar’s picture

Hi,
I tryed the #3 patch but it do not work for me.
When the end date is not inserted it will be set to start date value so I solved this with this code:

function hook_views_query_alter(&$view, &$query) {
  if( $view->name === 'name' && $view->current_display === 'block_1'){
    $query->where[2]['conditions'][1] = array(
        'field' => "field_data_field_xxxxx.field_date_value2 = field_data_field_xxxxx.field_date_value",
        'value' => array(),
        'operator' => 'formula'
    ); 
  }
}

but you have to be sure that the 'field_data_field_xxxxx' key is present in
$query->tables
and in
$query->table_queue
arrays.

I hope this will help someone.

B-Prod’s picture

Why not add an extra column in the date field tables, that stores the state of the "display end date" checkbox?

This would not cause trouble with the current behavior, but would allow to check against this column within a boolean view handler.

Note: this field should only be added when the end date field if optional.

B-Prod’s picture

Version: 7.x-2.5 » 7.x-2.x-dev
Status: Needs work » Needs review
FileSize
3.96 KB
FAILED: [[SimpleTest]]: [MySQL] 5,207 pass(es), 16 fail(s), and 2 exception(s). View

Here is a patch that matches the proposal exposed in comment 41.

There is an update hook for the existing fields.

The "has_todate" column is an integer, and I didn't modify the views handler to specify a boolean handler, but it can be easily improved, if necessary.

Status: Needs review » Needs work

The last submitted patch, 41: date-views-filtering-of-optional-end-dates-1635810-41.patch, failed testing.

B-Prod’s picture

Seems like the tests related to the migrate module are outdated...

thinkyhead’s picture

I'm going to suggest that if we need the end date to be "never" instead of being populated with the start date, there are a couple of potential solutions already existing. One option is to provide some value that stands in for "never" in the end date (such as 9/9/9999) and then do special display handling for that date. (Users can't be expected to enter 9/9/9999 in exposed forms, but we can instead show a "never" checkbox that hides the end date field.)

Another option is to add a boolean "No End Date" checkbox field to your content type (and your view exposed form) and do some extra filtering (with Views-Or module if needed) based on that.

I happen to think, however, that equating No End Date with Start Date was a bad early decision. Date module should be able to distinguish between a single date field and a date range field, and to filter date ranges more smartly.

If Date itself cannot now distinguish between a range with no end date and a range with a single date, clearly it needs fixing. These are not the same thing.

doitDave’s picture

What #44 says. The behaviour is unexpected to everyone in this thread, so it is probably to many more, and so it should be remedied.

Suggestion (rough, and without digging into code so far):
Turn "End date" from a bool field into an option field (0, 1, 2) where 0 and 1 stay unchanged but 2 means "never ending".
Depending on how it is saved internally, probably add a bool column to the field data table, saying "field never ends".

While this would not break any existing use, with some simple changes (views handler, display handler) date field would work as expected for both people who wouldn't consider the status quo an issue and those who do.

I desperately lack time, however, if there were a serious signal to have this in a next release, I would invest some hours for this.

Also sorry in case I should have overlooked an existing similar suggestion in the thread. :)

johnv’s picture

ITMT, #38 is working for me:
- Apply the patch to the 'Views' module (!)
- Add your date field as a FIELD (you might exclude it from the display)
- Add the "Global comparison", and add the two fields.

Donnyboypony’s picture

FileSize
3.57 KB
FAILED: [[SimpleTest]]: [MySQL] 5,302 pass(es), 16 fail(s), and 2 exception(s). View

#41 provides a solution nicely.

The patch however has an issue during the update of old field values. It does the reverse of what you expect.

This should fix the update issue.

Thanks B-Prod!

Donnyboypony’s picture

Status: Needs work » Needs review

Status: Needs review » Needs work

The last submitted patch, 47: date-views-filtering-of-optional-end-dates-1635810-47.patch, failed testing.

Status: Needs work » Needs review
jmuzz’s picture

Status: Needs review » Needs work

Wonder why this test didn't get run. All the results are gone too.

jmuzz’s picture

Status: Needs work » Needs review

Will this make a test happen?

jmuzz’s picture

Status: Needs review » Needs work

Guess not.

The patch prevents migrations to effected fields as the new column is not covered for them and it can not be null.

Shiraz Dindar’s picture

Here is the same patch from #47 with an updated hook number so that it runs on the current stable date module.