Struggling with datetime filtering in API

APIs
1

Hi,

I’m struggling with filtering dates via the API.

What I’m trying to achieve

I have a Words table which has a due field, which is a DateTime. There’s also a “has many” relationship between the Users table and this table.
I want to get the words which have a due timestamp in the past, for a given user.
For context, the words in the table have due dates that range from a few hours in the past to the distant future. In the examples I use a date in 2024 instead of the current timestamp, so I would expect and empty list to be returned.

Approach 1: with the links

GET "/api/v2/tables/USERTABLE/links/WORDSTABLE/records/1?fields=Id,word,due&where=(due,lt,2024-01-01 00:00:00+00:00)"
=> returns ALL the records

Playing with the where clause:

  • where=(due,lt,fhdfsdf) => returns all the records
  • where=(fdsfsfw,ciwoioweioew,sdsfdsfs) => returns all the records

… OK, I think the where clause is just being completely ignored

Approach 2: without the links

GET "/api/v2/tables/WORDSTABLE/records?fields=Id,word,due&where=(due,lt,2024-01-01 00:00:00)"
=> returns 422: 2024-01-01 00:00:00 is not supported.

OK, I’ll try other formats:
2024-01-01 00:00:00+00.00 is not supported. (I did encode the + to %2B to get the + to be taken into account)
2024-01-01 00:00:00+00:00Z is not supported.
2024-01-01T00:00:00+00:00Z is not supported.
2024-01-01T00:00:00+00:00 is not supported.
2024-01-01T00:00:00 is not supported.
2024-01-01 00:00 is not supported.

I have probably tried many more combinations, without success.
I have tested that (due,lt,exactDate,2024-01-01) does work as expected - but it’s not the behaviour I want, since I need the time of day to be taken into account.

Approach 3: with a formula field

Next I created a formula field called overdue with the simple formula due <= now(). In the UI it works. Initially it was showing as 0 or 1, and after adding formatting, it shows as a checkbox, reflecting the fact that it’s really a boolean. If the record is due in the recent past it’s checked, and if it’s in the future it’s unchecked. After formatting as a checkbox it can’t be unformatted, but I’ve tried before and after applying this formatting. The output that follows is with that formatting applied.

GET "/api/v2/tables/WORDSTABLE/records?fields=Id,word,due,overdue&where=(overdue,eq,true)"
=> returns an empty list. Oh, great! Hold on…
(overdue,eq,false) => also returns an empty list…
(overdue,eq,0) => also returns an empty list…
Wait, let’s try this:
(overdue,neq,false) => returns all records
Oh?
(overdue,neq,true) => returns all records
Oh.

(overdue,ge,0) => Filter verification failed, errors: Operation ge is not supported for type Checkbox on column overdue.
Fair enough. Note that before applying the checkbox formatting, this was accepted and returned (I think) nothing.

I don’t know what else to try… for now I’m retrieving ALL records and filering in my backend logic, which makes no sense at all.

Would love some help!

Thanks,
P.

Notes: I am self-hosting nocodb 0.263.8 via the latest docker image at nocodb/nocodb (on ARM64), with a MariaDB instance behind it.

2

hi @Pierric , yes with link the where clause is ignored.

for approach 2, you can use where=(due,lt,exactDate,2024-01-01 00:00:00)
though please keep in mind that the datetime is stored as utc, so it may need some adjustment.

for approach 3, with mysql db you need to use 0 and 1 than true or false. For better consistency, use the IF formula like this: IF(due <= now(), true, false)

3

Thank you @fendy !

Updating the definition of overdue to IF(({due} < now()), true, false) did not work, as (overdue,eq,0) and (overdue,eq,1) both return no items (same with true and false as well).

However, combining exactDate with a timestamp DID work. I hadn’t tried this combination before (to me it sounds like exactDate implies we can’t provide a DateTime, or that it will truncate the time part to compare dates only; but it does seem to honor the time part in the comparison).

Regards,
Pierric.

4

hi @Pierric , the formula part works for me on mysql 0.263.8. Perhaps it’s different on mariaDb, can you share your env connection with all secrets redacted?

5

My bad @fendy,

I must have had my containers mixed. I thought I had a mariaDB backend somewhere that nocodb connected to, but it doesn’t seem to be the case, neither is there a mysql instance apparently. Rather, I have a mounted volume with an SQLite database in it!