Hello! I'm trying to make a relative date query in elasticsearch SQL. I have this code:
SELECT * FROM index WHERE date > CURRENT_DATE - INTERVAL '30' DAY
Which seems to be correct. However when I run it, it gives the following error:
[essql] > Unexpected error from Elasticsearch: [parse_exception] failed to parse date field [2019-07-02T00:00Z] with format [HH:mm yyyy-MM-dd]: [Text '2019-07-02T00:00Z' could not be parsed at index 2]
Can you please help me understand this. Is there a way to reformat the date format output by 'CURRENT_DATE'? Do I need to reformatting the way I store my dates in elasticsearch?
This is driving me up the wall. Any help is hugely appreciated.