Would someone be able to post an example of filtering a Dataset’s Data based on a date value?
It’s easy to filter on a single column that is a String Value. The example below works:
/api/dataset/data/1?Location=Room 1
However the same format above does not work for a date field.
I want to query the API for dataset results where the date column that falls within a specific date range.
/api/dataset/data/16?date=2018-04-17 17:30:00
where
16 is dataSetId
date is the column in this dataset with date
Thanks Peter, I just checked and this works for a single word column name, but doesn’t seem to work with a column name that has a space in it.
Working: /api/dataset/data/16?Start=2018-04-17 17:30:00
(column renamed) Not working: /api/dataset/data/16?Display Start=2018-04-17 17:30:00
Where Start / Display Start are the column names. Have tried enclosing in quotes etc, but doesn’t seem to help.
Also, is there any way to filter between times?
E.g. Date >= 2018-04-17 17:30:00 AND <= 2018-04-17 23:59:00?
EDIT: Have also tested with a standard string column which also doesn’t seem to work when the column name contains a space.
Not working:** /api/dataset/data/16?Event Name=Test
It is not really recommended to use spaces or any special characters in dataset column’s headings, no matter if you’re filtering/ordering it via API or directly via mysql.
You can filter the results between two dates, there are multiple examples of that on this very site.
There is order column on the Ticker / dataSet view widgets specifically for that and that’s what you should be using to show the content on the players, I’m not sure why you’re trying to use API call there.
via API, well it is a simple GET call in which you can pass certain parameters (column names and values in this case) you can’t really pass mysql filter query in it per se.
Hi Peter,
Happy to adjust my column names for this to work, however this seems like an odd limitation.
If an end user is inputing data to a dataset, then you would want the column names to be human readable which means in some cases, with spaces.
The reason I ask about the API call:
We use Xibo to display Meeting Room Bookings via Datasets.
It works really well until there are no events to display - this is where options become limited to static content in the ‘No Data to Display’ option of Tickers/Dataset Views.
I’m aiming to build an app that will query the API for events in a date/time range and only display the Events Layout when events are actually scheduled. This means promotional content can be displayed during periods of no events.