API - Filter by date

Support
1

Hello everyone!

Some years after my first use of Cockpit CMS. Here I come again!

I’m stuck, i don’t find how to filter only a certain date (of _created) on the API (because I have more than 1000 entries added everyday).

Example : /api/collections/entries/scans?filter[_created]=14/07/2021

I would love to get all the entries created today only.

Thank you for your precious help!

Alexis

2

Afaik _created and _modified are saved as unix timestamps (in seconds).
Did you try to use a $lt / $gt filter to filter for items with
end-of-day-as-unix-timestamp < value < begin-of-day-as-unix-timestamp ?

See also Filtering by date using PHP

3

Thanks for your answer.
Can’t make it work.

/api/collections/entries/scans?filter[_created][$gte]=8&filter[_created][$lt]=16

403 Forbidden?

4

I assume you either missed to send a token with the request or the permissions for the collection need to be adjusted. Hence the 403 - Forbidden

Authenticate via token query parameter

For the token you have to use

  • a custom key, with the single rule /api/collections/entries/scans (via Cockpit > Settings > API Access)
  • a user token for a custom group which has in itself the correct permissions to access the api
  • your ‘master api-key’ (which I would not advise because if somebody gets hold of it your whole API can be accessed by them)
/api/collections/entries/scans?token=YOURTOKENHERE

(Other options would be to send the token in the request data as parameter token or in the header as Authorization: Bearer YOURTOKENHERE)

Collection Permissions

You have to adjust the API permissons for your collection via ‘Collection > Settings > Permissions’.

You either have to

  • assign the custom group of the requesting user and set Group: allow view entries (edited)
  • or, if using a custom key, set Public: allow view entries

Filter by day

Assuming we’d look for “today”, 16th of July, we’d have to translate start and end of the day into unix timestamps:

$timestampStart = mkdate(0, 0, 0, 7, 16, 2021); 
# -> 1626386400
  • 2021-07-16 00:00:00 = 1626386400
  • 2021-07-16 23:59:59 = 1626386400 + 24*3600 - 1 = 1626472800 - 1

As we’re going to use $lt less than and $gt greater than we should actually add/subtract a second here

  • newer than: 2021-07-16 00:00:00 - 1second = 1626386400 - 1 = 1626386399
  • older than: 2021-07-16 23:59:59 + 1second = 1626472800

And the query params for the filter would then look like that

filter[_created][$gte]=1626386399&filter[_created][$lt]=1626472800

And here comes the full query

/api/collections/entries/scans?token=YOURTOKENHERE&filter[_created][$gte]=1626386399&filter[_created][$lt]=1626472800
1 Like
5

Hello @abernh

Thank you for your detailled answer.

But can’t make it work. With https://XXXXXXXX/api/collections/get/scans?token=MyToken&filter[_created][$gte]=1626386399&filter[_created][$lt]=1626472800

I have a 403 ## Forbidden

6

Did you set the necessary permissions?

Are you using

  • a user-api-key with “group: allow view entries”
  • or a custom-key with “public: allow view entries”
    ?

Before you go into all the filter-applying just make sure you get the basic request working

Once this is not giving you a 403 anymore, you should be fine with the filters as well.

7

Addition … I just saw that my suggested /get is indeed deprecated and your initial /entries is the preferred way to go.

/api/collections/entries/scans?token=YOURTOKENHERE
8

Hi,

It’s totally public, i tried with token too.

/api/collections/get/scans?token=YOURTOKENHERE (is working)
/api/collections/entries/scans?token=YOURTOKENHERE (is working)

Here the server for trying: https://api.scan.100plus.be/api/collections/entries/scans?limit=10

9

Addition. On the remark to add/subtract a second here and there in order to make the query request exactly ‘today’ I completely missed the gte and lte filters

  • $lt : less than
  • $lte: less than or equal
  • $gt : greater than
  • $gte: greater than or equal

But back to the issue at hand - this is not a permission issue, as Unauthorized would be a 401.

This is something different. I assume you are using a real MongoDb for your data and not Lite?
I coulnd’t find any reason in the Cockpit source code to generate a 403 for using a filter with a comparison query operator when using Lite.

So maybe it is the MongoDb driver that throws an exception that in return causes the request to be interpreted as “forbidden”. It is called here

Maybe that allows you to add some debug info around it.

10

Given the operator works at some point it seems there is another way to filter by range:

This would make the query look like

/api/collections/entries/scans?filter[$and][][_created][$gte]=STARTTIME&filter[$and][][_created][$lte]=ENDTIME

Both variants work nicely for me in my LiteDb test setup.