0

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.

bebe
  • 3
  • 2
  • Check below thread, [related to get 3 days ago date](https://stackoverflow.com/questions/31852062/get-date-of-3-days-ago)might help you on this: – DB08 Mar 09 '22 at 01:55

1 Answers1

0

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

systematical
  • 68
  • 9
  • I have also attempted using CURRENT_DATE and DATE_ADD by subtracting 1 day, however it still includes data from TODAY and YESTERDAY, rather than only data from YESTERDAY. – bebe Mar 09 '22 at 01:49
  • can you add another condition like "and Date < CURRENT_DATE". I typically work in T-SQL (MS SQL) but you should be able to add another condition or "and Date <> CURRENT_DATE" – systematical Mar 09 '22 at 02:04
  • Thank you for your help! timestamp < CURRENT_DATE() - INTERVAL 1 DAY worked!! – bebe Mar 09 '22 at 03:59