Date Range filter drops records on boundary days
When 2020-01-01 - 2020-01-31 is selected as a date range, records with a date near the start of the 1st or the end of the 31st are not included in the results.
This issue has both a frontend and backend component:
- On the frontend, the values passed along with the
query_payload
underfilters
are2020-01-01T06:00:00.000Z
and2020-01-31T06:00:00.000Z
. The exact time will differ based on the time zone of the user. In my case, that's Central Time: UTC-6. Instead, the sent values should be2020-01-01
and2020-01-31
, without a date component. - Once the frontend issue is fixed, the
WHERE
clause generated by the backend will look likecolumn >= '2020-01-01' AND column <= '2020-01-31'
. This is fine ifcolumn
is of timedate
, but not when it is a timestamp instead, since'2020-01-01'
and'2020-01-31'
will be converted into timestamps, with a time at the beginning at the day, instead of the end. Instead, the generatedWHERE
clause should beDATE(column) >= '2020-01-01' AND DATE(column) <= '2020-01-31'
to ensure times are not taken into account.
/cc @derek-knox
Edited by Melty Bot