Canvas ESSQL current date - 30 days query

Elastic Stack Kibana
1

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

2

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

3

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

4

@stefws this is our main page for the list of functions available in ES SQL: https://www.elastic.co/guide/en/elasticsearch/reference/7.x/sql-functions.html

Regarding the specific versions, you'd have to look for the release notes of those versions. It might also help the github issues created for features/bugs, where the associated PR that implemented/fixed the feature/bug has version labels.

5

@Andrei_Stefan Thanks for the pointer :slight_smile: