Add ability to filter ranges with date fields

I also did some manual tests and it does not seem to work.

The code does the assumption that the fields store timestamps but is not the case for date fields from core.

If you add a timestamp field is not detected as date field but numeric.

I'll raise the priority since this is listed in the description of the module and the stability is now RC but trying to make it work is kinda misleading.

I'm also banging my head with this date range field issue. I'm not a big fan of the creating another timestamp field and populating that from the actual date range field as mentioned in #8...

Has anyone had a chance to do any analysis where things boil down when using date range fields?

Cheers,
Markus

I was looking into this yesterday. The reason for the issue seems to be exactly as described in #4 by @espurnes.

@espurnes provided one workaround in #8 which is based on using timestamps instead of date fields.

The timestamp based approach was not feasible for me, so I ended up using another workaround which is based on hook_views_query_alter() instead of this module. I will replace my ugly hack with this module once this date range bug has been resolved. For the reference of others, here's my hook_views_query_alter() implementation.

use Drupal\views\ViewExecutable;
use Drupal\views\Plugin\views\query\QueryPluginBase;

/**
 * Implements hook_views_query_alter.
 *
 * Alters the 'calendar' view query so that it filters the data by given
 *  contextual filter argument.
 *
 * Views contextual filters do not support 'between' operators for querying
 * a range of records based on given argument. Contributed module
 * contextual_range_filter provides this exact functionality but it has a bug
 * with date ranges. For that reason, we are currently altering the Views
*  query with this alter hook.
 *
 * @see https://www.drupal.org/project/contextual_range_filter/issues/2944490
 *
 * @todo Replace this hook implementation with contextual_range_filter
*  when issue #2944490 has been resolved.
 */
function MYMODULE_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  // Limit the altering to the desired view only.
  if ($view->id() == 'calendar') {
    // Traverse through the 'where' part of the query.
    foreach ($query->where as &$condition_group) {
      foreach ($condition_group['conditions'] as &$condition) {
        // Check if the condition is for the date range field.
        if (strpos($condition['field'], 'field_date_range_value') !== FALSE) {
          // Rewrite the condition with our own condition.

          // Get the contextual filter argument value.
          $url_argument = current($condition['value']);

          // Parse the $url_argument as you see fit.
          // Remember to validate the raw user input.
          // This example sets hard coded values for the sake of example.
          $from = '2018-04-01';
          $to = '2018-04-18';

          $condition = [
            'field' => 'node__field_date_range.field_date_range_value',
            'value' => [$from, $to],
            'operator' => 'BETWEEN'
          ];
        }
      }
    }
  }
}

Following the comments in #4 I think we could easily update the DateRange class to call the getDateField() method on the Query Object. getDateField() takes a string_date param, which if set to true, everything works fine.

I'm also noticing the normal timestamp fields, the $plugin_id is "date_xx" but if you are using a datetime field it is "datetime_xx". So we can use that to figure out how to call getDateField().

Need to investigate a little further and hopefully I'll be working on a patch today.

This could be going a few different ways. I believe this is working for my use case so far. I'd like to test on other date contextual filters.

Fixed some coding standards

is there a reason why the getFormula wasn't changed to getField, as per #4 & #12? I can't get this filter to work without it at all. Here's a patch for that.

@cachesclay This was a little bit ago so I don't remember how that played into exactly. Can you provide some more information on your setup and use case? Specifically the views config file with the plugin id?

I currently have the patch in #14 on a production site. I'm curious what your use case is to see whats breaking. #q4 might have only really fixed my use case. Thanks

@pingevt I was just trying to filter by a datetime field - giving it params: 2019-08-08--2019-09-15. Before using the patch in #15, no results, after I added it, I got the correct results. When I looked at the code, that $field var had a full query in it that was breaking the logic.

@cachesclay Were you using the patch in #14 as well? or only the one in #15?

Just trying to understand your whole process, thanks!

@pingevt - my bad - I didn't use the patch from 14, because it was working with a date range as I needed (2019-08-08--2019-09-15). I tried the patch from 14, but it didn't seem to fix that specific use case for me, so I held on adding it in.

@cachesclay - I'll be honest, at first I didn't understand what you were doing and just about to write a long message saying I have no clue how you go it to work... BUT I think we are using different arguments... I was using the `Date in the form of CCYYMMDD.` and looks like you were using the actual field argument. It took me awhile, but my arguments are formatted like "20190101--20190501" and yours had dashes. So these are completely separate use cases. And both behaving differently. I'll run down this rabbit hole for a bit and see what I can find.

I'll try and write some tests for this as well. I'm trying to get better at that, but writing tests are taking up a lot of time.

Coming into this discussion a bit late, but as this is a module I'd love to use, precisely in the date range context, I'm chiming in. In my use case I've got dates going back to 1952, and that content needs to be displayed so timestamps aren't an option for me either.

Looking at the comments above, I'm seeing multiple date formats CCYYMMDD, CCYY-MM-DD, etc. The code could be written to try and parse the filter parameter and try and guess the format, or it could be made explicit, as part of the settings for the filter in the view, or along with the settings in /admin/config/content/contextual-range-filter. Guessing the format IMHO is always a bad idea, although it would be more flexible.

I'm not versed enough with the contextual filter configuration on the view to figure out which is the more feasible option quite yet, but I'm very interested in helping get this issue moved to the resolved state.

As an aside, my use case only requires that the filter work down to the year, based on the current content of the site I'm migrating to D8

I've not been able to give this module the attention it deserves for some time.
In addition quite a few things seem to have changed in Drupal core/Views too.

I've just checked into 8.x-1.x a number of changes to the DateRange plugin, which wasn't working at all (anymore).

When specifying date ranges you can use most formats, e.g. 20200519--20200608 and
19May2020--today should work equally well.

More testing and tweaking may be required, but I believe the module is in better shape than it was weeks ago.

Thanks @Beakerboy for doing the initial porting work!

I have not dug into why, but the most recent release (rc3) makes the patch not apply and breaks the functionality.

I get a query that looks, in relevant part, like this:
AND ((DATE_FORMAT(
(DATE_ADD('19700101', INTERVAL node__field_call_date.field_call_date_value SECOND) + INTERVAL -14400 SECOND),
'%Y%m%d') BETWEEN '20191201' AND '20200531'))

The problem being that this statement:
"DATE_FORMAT((DATE_ADD('19700101', INTERVAL node__field_call_date.field_call_date_value SECOND) + INTERVAL -14400 SECOND),
'%Y%m%d')"

produces "19691231" for all values in the table. So no matter how you fix your parameters, the condition is never going to work.

@gbirch
Thanks for your report.
Bummer.
Need to find time to look into this.

In case it helps, it appears that the bad bit of SQL just quoted SHOULD be:

DATE_FORMAT(DATE_ADD(node__field_call_date.field_call_date_value, INTERVAL -14400 SECOND), "%Y%m%d")

Eek. This actually appears to come from core in views/src/Plugin/views/query/MysqlDateSql.php in MysqlDateSql::getDateField().

This is a total hack but this worked for me. I think the right solution is to figure out if that's a problem in core, but its going to be more difficult, I think, to get things changed in core for this, unless we can come up with some evidence that its buggy behavior there.

Changed this:

    $formula = $this->getFormula();
    $range_conversion = empty($this->options['relative_dates']) ? NULL : [$this, 'convertRelativeDateRange'];

to this:

    $formula = $this->getFormula();
    preg_match('/, INTERVAL (.+?) SECOND/', $formula, $matches);
    $formula = str_replace("'19700101'", $matches[1], $formula);
    $range_conversion = empty($this->options['relative_dates']) ? NULL : [$this, 'convertRelativeDateRange'];

in src/Plugin/views/argument/DateRange.php

@gbirch @jasondmichaelson

Thanks so much for your research and solutions.

I am however confused as I cannot reproduce the erroneous results that you are getting.
I created a simple View on page /mycontent (no Menu) with the Changed date as a Contextual Filter.
I transformed it to a Contextual Range Filter on the module's configuration page, admin/config/content/contextual-range-filter.
I then went back to the Contextual Filter form and ticked the Allow relative date ranges checkbox.

When I use the Views Preview pane and enter something like 2 weeks ago -- yesterday, I see this query being generated:

SELECT node_field_data.changed AS node_field_data_changed, node_field_data.nid AS nid
FROM {node_field_data} node_field_data
WHERE (DATE_FORMAT((DATE_ADD('19700101', INTERVAL node_field_data.changed SECOND) + INTERVAL 36000 SECOND), '%Y%m%d') BETWEEN '20200606' AND '20200619' )

Given the fact that today is 20 June 2020, it seems that Contextual Range Filter module has generated the correct (sub)clause, with "2 weeks ago" being translated to 20200606 and yesterday being 20200619
The 36,000 is explained by the fact that I'm in Melbourne, Australia, UTC +10 hrs.

Moreover, unlike @gbirch's experience in #23, on my system this query produces the correct results.

My system is MAMP stack on Macbook Pro, running:
Drupal 8.9.0 on Apache/2.2.34 with PHP 7.3.9 and MySQL 5.7.26

I agree with @jasondmichaelson that the "faulty" (if it is faulty) part of the above query is generated by core, not by the Contextual Range Filter module. And I also agree that a "hack" is better than no solution at all.

In addition to the above....
I applied the patch from #27 on my test system. The query thus generated changed from:

SELECT node_field_data.changed AS node_field_data_changed, node_field_data.nid AS nid
FROM {node_field_data} node_field_data
WHERE (DATE_FORMAT((DATE_ADD('19700101', INTERVAL node_field_data.changed SECOND) + INTERVAL 36000 SECOND), '%Y%m%d') BETWEEN '20200606' AND '20200619' )

to

SELECT node_field_data.changed AS node_field_data_changed, node_field_data.nid AS nid
FROM {node_field_data} node_field_data
WHERE (DATE_FORMAT((DATE_ADD(node_field_data.changed, INTERVAL node_field_data.changed SECOND) + INTERVAL 36000 SECOND), '%Y%m%d') BETWEEN '20200606' AND '20200619' )

This is not quite correct.
And on my system it is this second "fixed" query that produces NO results!
Exactly the reverse from what others have experience.
I guess this is because the Change date I've used is a timestamp (i.e. integer seconds since 1-Jan-1970), whereas others have had fields that are Drupal DateTime fields, which present themselves to MySQL as strings in this MySQL format: '2020-06-19' or '2020-06-19T23:59:59'.

NOTE: MySQL manual, https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#fun...

Have added a Date string fix and checked it in, while still also supporting the Authored On and Changed timestamps on Nodes.

Tagged as release 8.x-1.0-rc4.

Setting this to "Fixed"... until proven otherwise, which may not take long haha.

Actually, I think that did it.

@jasondmichaelson
Thanks for confirming.
Will leave it for a few more days, then will release 8.x-1.0.

Note: regardless of whether the date field used is a timestamp or a Date(Time), the (default) absoulte date format for use in a contextual range is the same as D8 core Views, which is 2020-12-31 (ie WITH hyphens).

However when the "relative dates" box is ticked you can also use phrases like "31 Dec" (this year) to specify absolute dates.