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
2020-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 be
2020-01-31, without a date component.
- Once the frontend issue is fixed, the
WHEREclause generated by the backend will look like
column >= '2020-01-01' AND column <= '2020-01-31'. This is fine if
columnis of time
date, but not when it is a timestamp instead, since
'2020-01-31'will be converted into timestamps, with a time at the beginning at the day, instead of the end. Instead, the generated
WHEREclause should be
DATE(column) >= '2020-01-01' AND DATE(column) <= '2020-01-31'to ensure times are not taken into account.