timezone logic incorrect

[kejedi]

i store all dates in UTC, but each user has their own timezone

however, when i set the filter to use the users timezone, the results are incorrect:

                DateRangeFilter::make('created_at')
                    ->timezone(Auth::user()->timezone),

when i do Today, it shows no results, because the auth user timezone in this case is America/Toronto and its almost 9pm

[malzariey]

Fixed with version 2.5

[bogusbd]

Hello, @malzariey I have a similar problem in version": "2.5.3"

With this code:

DateRangeFilter::make('created_at')->timezone( 'Europe/Tallinn'),

JS part when selecting today / yesterday works But the SQL query is generated wrong. Time is not converted to UTC from the selected timezone.

I receive the following SQL query when filtering:

  and (
    `created_at` between '2024-04-16 00:00:00' and '2024-04-16 20:59:59'
  )

but there should be this one (with a shift of 3 hours)

  and (
    `created_at` between '2024-04-15 21:00:00' and '2024-04-16 20:59:59'
  )

It seems the problem here: https://github.com/malzariey/filament-daterangepicker-filter/blob/9603326bf088d3802235351b08dc2a27b00982c2/src/Filters/DateRangeFilter.php#L225

And this piece of code fixes it:

        return $query
            ->when(
                $from !== null && $to !== null,
                fn (Builder $query, $date) : Builder => $query->whereBetween($this->column, [
                    Carbon::createFromFormat($this->format, $from, $this->timezone)->startOfDay()->setTimezone(config('app.timezone')),
                    Carbon::createFromFormat($this->format, $to, $this->timezone)->endOfDay()->setTimezone(config('app.timezone')),
                ]),
            );

but I'm not sure.... maybe someone else can take a look at this..

[malzariey]

Hello, @malzariey I have a similar problem in version": "2.5.3"

With this code:

DateRangeFilter::make('created_at')->timezone( 'Europe/Tallinn'),

JS part when selecting today / yesterday works But the SQL query is generated wrong. Time is not converted to UTC from the selected timezone.

I receive the following SQL query when filtering:

  and (
    `created_at` between '2024-04-16 00:00:00' and '2024-04-16 20:59:59'
  )

but there should be this one (with a shift of 3 hours)

  and (
    `created_at` between '2024-04-15 21:00:00' and '2024-04-16 20:59:59'
  )

It seems the problem here:

https://github.com/malzariey/filament-daterangepicker-filter/blob/9603326bf088d3802235351b08dc2a27b00982c2/src/Filters/DateRangeFilter.php#L225

And this piece of code fixes it:

        return $query
            ->when(
                $from !== null && $to !== null,
                fn (Builder $query, $date) : Builder => $query->whereBetween($this->column, [
                    Carbon::createFromFormat($this->format, $from, $this->timezone)->startOfDay()->setTimezone(config('app.timezone')),
                    Carbon::createFromFormat($this->format, $to, $this->timezone)->endOfDay()->setTimezone(config('app.timezone')),
                ]),
            );

but I'm not sure.... maybe someone else can take a look at this..

Try v2.5.4 Fixed. Thanks for raising the issue.

[bogusbd]

Hi, @malzariey thanks for the quick fix, but no, v2.5.4 doesn't work correctly

Now the time that comes from JS (which is already in the user timezone) is again converted to the user timezone. And I get UTC +3 + 3

I selected "today" and get this:

`created_at` between '2024-04-16 03:00:00' and '2024-04-17 02:59:59'

but I'm expecting to get it:

  and (
    `created_at` between '2024-04-15 21:00:00' and '2024-04-16 20:59:59'
  )

exactly this code works:

      Carbon::createFromFormat($this->getFormat(), $from,$this->getTimezone())->startOfDay()->timezone(config('app.timezone')),
                    Carbon::createFromFormat($this->getFormat(), $to,$this->getTimezone())->endOfDay()->timezone(config('app.timezone')),

It converts time from user timezone to UTC

[malzariey]

Hi, @malzariey thanks for the quick fix, but no, v2.5.4 doesn't work correctly

Now the time that comes from JS (which is already in the user timezone) is again converted to the user timezone. And I get UTC +3 + 3

I selected "today" and get this:

`created_at` between '2024-04-16 03:00:00' and '2024-04-17 02:59:59'

but I'm expecting to get it:

  and (
    `created_at` between '2024-04-15 21:00:00' and '2024-04-16 20:59:59'
  )

exactly this code works:

      Carbon::createFromFormat($this->getFormat(), $from,$this->getTimezone())->startOfDay()->timezone(config('app.timezone')),
                    Carbon::createFromFormat($this->getFormat(), $to,$this->getTimezone())->endOfDay()->timezone(config('app.timezone')),

It converts time from user timezone to UTC

Makes sense. Thank you for your attention. Made me notice an additional issue.

Should work now.