1

SITUATION:

  • Create a view of transactions (a custom entity in my case, but I don't think this is relevant)
  • Filter against Authored on (which includes both the date and time of creation)
  • Expose the filter
  • Choose Operator: Is between
  • Display the view with 10/01/2020 and 10/31/2020 as start and end dates for October
  • Note: I'm using the Date Popup module for date entry, which accepts mm/dd/yyyy

PROBLEM:

Transactions on 10/31/2020 are omitted. How can I include end-date transactions, short of telling users to put 11/01/2020 instead of 10/31/2020 (which is what seems natural to them)?

SOLUTION:

Thanks to @prkos in his answer below that a time of 23:59:59 must be included with the end date to compare up to the last second of the day. After learning this, I added a custom submit function to the exposed filter that appends the end time, like so:

use \Drupal\Core\Form\FormStateInterface;

/**
 * Implements hook_form_FORM_ID_alter().
 */
function mymodule_form_views_exposed_form_alter(&$form, FormStateInterface $form_state, $form_id) {
  // Use array_unshift() so custom submit runs before default submit
  array_unshift($form['#submit'], '_my_submit_function');
}

function _my_submit_function($form, FormStateInterface $form_state) {
  $created = $form_state->getValue('created');
  if ($created['max']) {
    $created['max'] = $created['max'] . ' 23:59:59';
    $form_state->setValue('created', $created);
  }
}

Thanks to @No Sssweat for pointing out in his accepted answer below that array_unshift must be used to make the submit function run before the default. Initially, I simply used

$form['#submit'][] = '_my_submit_function'; // Don't do this. It doesn't work

See the accepted answer below from @No Sssweat for an alternative method of solving the problem using views_query_alter. I tried this and it worked as well, although in the end I chose the simpler of the two solutions.

UPDATE:

Turns out that, although the "simpler" hook_form_alter solution appeared to work, changing the value of the filter by adding a time breaks the filter if you move to another page in the list because the filter is passed via the query string to the page and it includes the time, which won't display in the date field. Given this, I reverted back to the views_query_alter approach, which doesn't have the same problem. My simple code is

function mymodule_views_query_alter($view, $query)
{
  if (!empty($query->where[1]['conditions'])) {
    foreach ($query->where[1]['conditions'] as $key => &$value) {
      if (strpos($value['field'], '.created <=')) {
        $e = explode(' ', $value['field']);
        $e[2] += (24 * 60 * 60) - 1;
        $value['field'] = implode(' ', $e);
      }
    }
  }
}

Note that I have switched from using IS BETWEEN to using two, separate tests against the Author On variable, on with >= and one with <=. You can see in the code that I'm modifying only the <= instance.

1

It's a core bug, as @prkos mentioned, end time is assumed 00:00:00 when it needs to be 23:59:59

/**
 * Implements hook_form_FORM_ID_alter().
 */
function mymodule_form_views_exposed_form_alter(&$form, \Drupal\Core\Form\FormStateInterface $form_state, $form_id) {
  if (!empty($form['created']['max'])) {
    // use array_unshift() so custom submit runs before the default submit.
    array_unshift($form['#submit'], '_fix_max_date_submit_function');
  }
}

function _fix_max_date_submit_function($form, FormStateInterface $form_state) {
  $date_filter = $form_state->getValue('created');
  if ($date_filter['max']) {
    $date_filter['max'] = $date_filter['max'] . ' 23:59:59';
    $form_state->setValue('created', $date_filter);
  }
}

Alternative solution

You might have to use this solution if you don't like the default Y-m-d formatting and rather have a m/d/Y input for example. Below I use Y-m-d, but you can always change it.

It's a core bug, as @prkos mentioned, end time is assumed 12:00:00 AM when it needs to be 11:59:59 PM

If you enable Views sql query at /admin/structure/views/settings you'll see the query is like:

enter image description here

So all you have to do is swap the last timestamp with the corrected timestamp that takes 11:59:59 PM into account.

You can do that with hook_views_query_alter, but hold your horses, this hook can only run inside of mymodule.views_execution.inc file, so create that file and then you can do

<?php

use Drupal\Core\Datetime\DrupalDateTime;
use Drupal\views\Plugin\views\query\QueryPluginBase;
use Drupal\views\ViewExecutable;

/**
 * Implements hook_views_query_alter()
 */
function mymodule_views_query_alter($view, $query) {
  if (!empty($query->where[1]['conditions'])) {
    foreach ($query->where[1]['conditions'] as $con_key => $con_val) {
      $has_created_date_condition = strpos($con_val['field'], 'node_field_data.created') !== FALSE;
      if (($has_created_date_condition)) {
        // timezone set in /admin/config/regional/settings, will get "Users may set their own time zone" if checked.
        $timezone = date_default_timezone_get();

        $min_date = $view->exposed_data['created']['min'];
        $min_date_obj = DrupalDateTime::createFromFormat('Y-m-d', $min_date, $timezone);
        $min_date_timestamp = $min_date_obj->getTimestamp();

        $max_date = $view->exposed_data['created']['max'];
        $fixed_max_date_obj = DrupalDateTime::createFromFormat('Y-m-d h:i:s A', $max_date . ' 11:59:59 PM', $timezone);
        $fixed_max_date_timestamp = $fixed_max_date_obj->getTimestamp();

        $query->where[1]['conditions'][$con_key]['field'] = 'node_field_data.created';
        $query->where[1]['conditions'][$con_key]['value'][0] = $min_date_timestamp;
        $query->where[1]['conditions'][$con_key]['value'][1] = $fixed_max_date_timestamp;
        $query->where[1]['conditions'][$con_key]['operator'] = 'BETWEEN';
      }
    }
  }
}
5
  • Thanks (again) @No Sssweat. You seem to be saving me all the time. This solution works perfectly. Nov 12 '20 at 17:16
  • For the benefit of others who may see this, mymodule.views_execution.inc goes in your modules/custom/mymodule directory. Also, because I'm using a custom entity of type transaction, 'node_field_data' is in my case 'transaction_field_data'. Finally, the Filter id for the Authored on filter must be 'created' for this code to work. Nov 12 '20 at 17:45
  • A curiosity question @No Sssweat if you have time: Any idea why modifying the form state in a custom submit function as I explain in my UPDATE above doesn't work? In addition to tacking on the time, I tried simply replacing 2020-10-31 with 2020-11-01 in the submit function as a test. That didn't work either. Nov 12 '20 at 17:47
  • @MarshallMorrise I took your word for it, I guess you must of done something wrong as when I tried, it worked. Perhaps you didn't do array_unshift part. I had used views_query_alter with D7 to solve this exact same problem, so I decided to stick to that known to work approach, but form_later with custom submit sure looks to be a quicker & smarter solution. Updated my answer
    – No Sssweat
    Nov 13 '20 at 4:57
  • 1
    You're absolutely correct @No Sssweat. I failed to use array_unshift, so my custom submit was running after the default submit. That's what was keeping it from working. I agree with you that the submit approach is more straightforward than intercepting the views query. I'm using it with the Date Popup module and it seems to work fine, even though user input is mm/dd/yyyy, Again, thank you. Nov 14 '20 at 3:32
1

I agree this implementation lacks some UX polishing.

The reason why that day is omitted in your case is because you're not including the time, so a time of 00:00:00 is assumed, which is the beginning of the day and most of that day's time will be excluded. The first day also assumes it, but because it's the beginning of the range it includes the whole day.

The solution is to use the time along with the date, and give clues to users to orientate them with using Placeholder example values and the Filter field Description:

Empty form:

Views Date Exposed Date Placeholder

After filling in:

Views Date Exposed filled in Time

You can come up with better UX Label and Description, I'm just demonstrating a good starting point.

3
  • Thanks for this insight, @prkos. I'm using the Date Popup module, which "provides a HTML 5 date popup for all views filters", so dates are entered in mm/dd/yyyy format, with mm, dd, and yyyy each being a separate field. There doesn't appear to be a way to control which date filter fields a popup is applied to; seems to be all or nothing. I'll try some sleuthing to see if I can intercept the second date to tack on 23:59:59 before it gets used by the view. Otherwise, I'll either live with the off-by-a-day problem or ditch the Data Popup module (though I really like it). Nov 11 '20 at 23:42
  • Yes, I like Date Popup too, it provides some nice usability, although it clearly fails in this case with the time missing. But you can try other similar modules that have the time picker option and see if they work for you. Here are some you might try: Datetime Range Popup, Date Popup, Bootstrap Materialize DateTime Picker, Date Popup Timepicker.
    – prkos
    Nov 12 '20 at 3:35
  • Again, thank you @prkos. Given your explanation, I thought I would add a custom submit function to the views filter to tack on the time, but as I explain in my UPDATE above, it doesn't have any effect. Nov 12 '20 at 4:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.