DateTime filter incorrectly uses whereDate when being applied with only one date selected

[simon-roland]

• Laravel Version: 10
• Nova Version: 4.23.0
• PHP Version: 8.2

Description:

When using filterable() on a datetime field, the filter has two inputs. If only one of the inputs is filled, the filter is applied using the builtin whereDate query funtion in Laravel. This will cause incorrect results to be shown, because the whereDate function only considers the date, not the time.

Detailed steps to reproduce the issue on a fresh Nova installation:

Database timezone: UTC App timezone: CET (UTC+1)

Record 1: 2023-05-08 23:30:00 UTC Record 2: 2023-05-08 22:30:00 UTC

From filter input: 2023-05-09 CET ->2023-05-08 23:00:00 UTC

The query using whereDate() becomes: WHERE date(created_at) >= '2023-05-08'

Both records are shown, when only the first should be a match.

The query should have been: WHERE created_at >= '2023-05-08 23:00:00'

Fix

Simply replace whereDate() with where() on line 189 and 192 of nova/src/Fields/DateTime.php

[crynobone]

Hi there,

I'll investigate this matter but at the moment you can replace the filter using the following command:

Date::make('Created At')
     ->filterable(function ($request, $query, $value, $attribute) {
          [$min, $max] = $value;

          if (! is_null($min) && ! is_null($max)) {
               return $query->where($attribute, '>=', $min)
                    ->where($attribute, '<=', $max);
          } elseif (! is_null($min)) {
           return $query->where($attribute, '>=', $min);
          }

          return $query->where($attribute, '<=', $max);
     }),

[github-actions[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.