I want to filter baserow results using the API based on a timestamp (data + time). As far as I can see in the documentation, a later_than can only be done based on a date, not on a datetime. The same goes for the formula-builder in the baserow UI.
Regards, Jan
Hi!
Could you show API call you’re making?
Hi, @cezary, the fulter builder only gives me the possibility to insert a data, not a time.
Yes, this seems to be a bug in date picker. There’s a fix coming up: Data source does not allow filtering based on datetime (#3181) · Issues · Baserow / baserow · GitLab
Ah, that’s great! So, this means that using a date-time through the API is already possible?
And, if so, how should a datetime parameter be formatted?
Something like: yyyy-mm-dd:hhMMss, or an ISO format or something else?
As far as I can see in the documentation, a
later_thancan only be done based on a date, not on a datetime
This is a bit more complex topic. First of all, there are two flavors of date filters. They differ internally in the way date comparison is executed. Old date_before/date_after operate on a single value, while date_is_before/date_is_after operate on lower/upper bounds of a range (datetime, datetime+1 day). Both accept datetime values, but the accepted value format is slightly different. Old filters are marked as deprecated. They are not available in UI, but will work with API. Note that they may be removed at some point.
Old filters accept "Europe/Amsterdam?2024-10-11 12:13:14" value, new requires additional operator: Europe/Amsterdam?2024-10-11 12:13:14?exact_date. For datetime part you should be able to send full ISO value as well.
New filters operate on a date range, and because of that datetime values may require an adjustment to have correct results. As I wrote before, each filter operates on a range datetime, datetime+1 day. Depending on a filter, lower or upper bound can be used to create a database query. For example, date_is_on_or_after will use lower bound with >= operator (so the internal expression is an equivalent of row_value >= datetime, and date_is_after will use upper bound with that operator (so the internal expression is an equivalent of row_value >= (datetime + 1 day). So, if you want to filter date_is_after Europe/Amsterdam?2024-10-11 12:13:14?exact_date, you actually should set filter’s value to Europe/Amsterdam?2024-10-10 12:13:14?exact_date for that specific filter.
Note that we’re working on improving datetime filtering part.
Hi @cezary Tx for your detailed reply, that is really helpful!