Canvas ESSQL current date - 30 days query

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.
Thanks

@samgs you are right and thank you for posting your question. This is a bug. We do not set a specific format in the range filter we create and, implicitly, Elasticsearch takes the format of the date constant we pass in resulting from the CURRENT_DATE - INTERVAL '30' DAY calculus. And that format is yyyy-MM-dd'T'HH:mm:ss.SSSV.

I've created an issue and I'll take care of it: https://github.com/elastic/elasticsearch/issues/45139.

I don't think there is any workaround at the moment other than using the default format for your date fields.

1 Like

Where might we find doc on ES SQL and more specifically what functions have been implemented in what version and howto use them?