Trying to implement a date range filter on a datagrid. The value coming in from Xano is a timestamp. I have it displayed in a date cell in the datagrid. Trying to filter where the data is ‘>=’ the start date selected in the date range picker - not working. Config is below. Am I doing something wrong? I do see that the dates are formatted differently in the variable associated with the date range picker compared to the timestamp coming from Xano. Is that the issue?
I made quite a bit of progress. I installed the Date extension to get access to the Date no-code formulas. Still struggling to understand the formats I see in the variable associated with the date range picker element though. Please see loom and would appreciate being pointed to the best resource to help me understand this. Thanks.
The dates provided by the datepicker are in the ISO format (using UTC timezone). It will differ from the one you’re selecting in the UI because UI is showing you the date in your own local timezone (converted on the fly).
Be aware if you dont need the timezone you can switch the date mode on the datepicker from Datetime to Date so it will keep only the date “2023-06-01” part.
You can also force a specific timezone (UI related) if you need it to be different from your local.
Just to help you understand how your dates works, you can see on Xano you have dates that are timezone aware (with the -07:00)
Jun 1 2023 6:18 PM -07:00 is equal to Jun 1 2023 11:18 AM UTC (2023-06-01T11:18:00Z)
Is equal mean, if you convert both in timestamp it should give you the same number.
Be aware your datepicker is currently using the datetime mode, meaning when you select a date it take your current time too, not only the day, it can impact your filter when you’re comparing them.
Thanks @Alexis. I changed the datepicker element from Datetime to Date and got it working. In the filter I have to convert the datepicker variable values to timestamps, and for the End Date only I have to add one day - details in Loom. Am I doing this right? Thanks!
This is because your date in Xano are the 1 June at 6h so they are not lesser or equal to 1 June, because the hour is important in the comparison too, its not only comparing the date, its comparing datetime.
What you can do is set the hour, minutes and seconds manually on the date so it always compare to the selected date at 23:59:59 instead of 00:00:00
Edit : For the clear button you have to build it yourself, you can put a button or an icon inside the input (or even outside), add workflow on it and change the variable value of your datepicker
OK thanks. I am effectively doing the same thing though by adding 1 day - right? I changed it to be less than, not less than or equal to, which means it would include up to 23:59:59:59 for that day I think. Seems to be working correctly so far.