I am looking to query data from yesterday, excluding the data from today. I have figured out how to do this when inputting a specific date and subtracting 1 day. However, I need it to be from today's date, for example timestamp = NOW() - 1d/d. This is on an element in Kibana Canvas, using Elasticsearch SQL.
This is what I have so far, however I would need to update the query everyday to change the specific date.
SELECT COUNT(XXX.keyword) AS Count
FROM "XXX"
WHERE XXX.keyword='XXX'
AND timestamp = '2022-03-09||-1d/d'
timestamp = NOW() - INTERVAL 2 DAYS
would not work because it includes data from TODAY and YESTERDAY, whereas I only want the data from yesterday.
Thank you.
CodePudding user response:
It looks like you want curdate() or current_date(). Have a look at the link below for more extensive info on how to query dates (relative and otherwise).
https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-datetime.html