ElasticSearch cube: filter by exact date

Skip to first unread message

Q&A Bot

Mar 23, 2021, 5:55:05 AM3/23/21
to seektab...@googlegroups.com

Elasticsearch field mapping
              "Some Date": {
                "type": "date",
                "format": "MM/dd/yyyy"
We tried to filter with a parameter but our query doesn’t seem to work:
  • "Filter Relex":      1=1  @SomeDate[ and "Some Date":field = "{0}":var ] 
This happens because in SeekTable there is no 'date-only' data type for parameters (only DateTime), and as a result dates are actually passed to the query as timestamps with "00:00:00" time.
There are 2 ways how this could be handled:

1) instead of 'exact' date condition you may filter by a date range; for this purpose an additional (hidden) parameter should be added to computate a 'next day' timestamp as described here: https://www.seektable.com/help/report-parameters-date-range
  This approach is universal and will work for filtering by any date range (current week, current month etc) and even if you want to filter by timestamps.

2)  format parameter's DateTime value to date string (in format that matches ES field mapping) with help of parameter's "Expression":
  Parameter["SomeDate"]!=null ? Parameter["SomeDate"].ToString("MM/dd/yyyy") : null
Reply all
Reply to author
0 new messages