Date time range parameter filter ignore end time

[javierpelado]

trafficstars

Issue Summary

Trying to create a query that gets a date and time range filter as a parameter, I noticed that the counter was different than doing it directly within the DB.

The problem is that the filter doesn't use the time at the end threshold. It is filtering out all the records with date time which the date is later than the date end of the range.

It should include in the result the records with the same date and below the time at that threshold.

Example:

• query using range date and time:

  After changing the end threshold of the range one minute more what changes the day, all the records from the previous day appear

Steps to Reproduce

1. Use the Query results source which will give you sqlite env.
2. Create a query that filters records using data and time range as a parameter in the form

         select
          createdAt
        from
          cached_query_3
        WHERE
           createdAt BETWEEN '{{ Range.start }}'
           and '{{ Range.end }}'
        order by
            createdAt
   

3. Fix the end threshold of the range with a time that will include records from the day of it. EG: {day} 23:59
4. See that in the results there are only records before {day}, none of the records during that day appear.

Technical details:

• Redash Version: Version: 10.1.0 (2589bef1)
• Browser/OS: Chrome Version 114.0.5735.198 (Official Build) (arm64) / Macos Ventura 13.4.1 (22F82)
• How did you install Redash: docker compose in and EC2 instance with external PostgreSQL at AWS Aurora

[justinclift]

Oh, that's interesting.

It seems to be truncating the Range variable there to just the date component, ignoring any included time component. That does sound like a bug.

[gaecoli]

For date range (with seconds), we should add timezone to fix this bug.

[gaecoli]

For date range (with seconds), we should add timezone to fix this bug.

And it should add new date format, like hours 23, minutes 59, seconds 59 to format.

[justinclift]

@gaecoli Want to try fixing it? :smile:

[gaecoli]

@gaecoli Want to try fixing it? 😄

I will try to do it!

[antoniocarelli-ancora]

@gaecoli / @justinclift Any news on this issue?