MONTH Interval conversion to use in DATE CLAUSE

[vaclav1]

Hello,

I have tried to use following SQL in kibana internal index:

SELECT dashboard.title FROM ".kibana*" WHERE updated_at > TODAY() - INTERVAL CAST(MONTH_OF_YEAR(TODAY()) AS interval_month) MONTHS

Unfortunately I get an error:
[essql] > Couldn't parse Elasticsearch SQL query. You may need to add double quotes to names containing special characters. Check your query and try again. Error: [parsing_exception] line 1:79: Invalid [INTERVAL MONTH] value [CAST(MONTH_OF_YEAR(TODAY()) AS interval_month)]: expected digit (at [0]) but found [C]

Tired as well to user CONVERT function and convert to integer, but this does not work either.

Can someone help? :_)

[joshdover]

Hi there!

I'm a bit unclear on exactly what you're trying to query for, but you may have more success using the DATE_TRUNC function for this query. I believe you're trying to find all dashboards updated since the beginning of the year? If so this query should work:

SELECT dashboard.title FROM ".kibana*" WHERE updated_at > DATE_TRUNC('year', TODAY())

DATE_TRUNC will "zero-out" the parts of the date up to the specified unit, in this case the year. So if TODAY() is 2020-05-21T12:45:41, DATE_TRUNC('year', TODAY()) would equal 2020-01-01T00:00:00.

If that's not what you're trying to get, let me know what you're trying to query for and I can help more

[matriv (Marios Trivyzas)]

INTERVAL expressions are literals, they cannot be constructed on top fields or scalar functions.

[vaclav1]

Thank you! Original idea was to get monthly/weekly reports. So I need to use TODAY() or any other function to get 1 of each month and as well current week. I guess I shall be able to use DATE_TRUNC fucntion like this? DATE_TRUNC('month', TODAY()), maybe DATE_TRUNC('week', TODAY())?

Ok got this message, do I need to install or configure it somehow?:

[essql] > Unexpected error from Elasticsearch: [verification_exception] Found 1 problem(s) line 1:69: Unknown function [DATE_TRUNC]

[matriv (Marios Trivyzas)]

DATE_TRUNC is available since 7.5.0: https://github.com/elastic/elasticsearch/pull/46473

[vaclav1]

That will be it! and there are probably no other solutions for 6.8?

[matriv (Marios Trivyzas)]

@joshdover

SELECT dashboard.title FROM ".kibana*" WHERE updated_at > DATE_TRUNC('year', TODAY())

Maybe you can do:

SELECT dashboard.title FROM ".kibana*" WHERE YEAR(updated_at) >= YEAR(TODAY())

?

[vaclav1]

But this compares year or? if there will be two dates in the same year how this is going to work?

[matriv (Marios Trivyzas)]

Could you explain what are you trying to achieve?

[vaclav1]

I want to filter from particular date until particular date. For example past month.

[matriv (Marios Trivyzas)]

Then you can use MONTH(updated_at) >= MONTH(today()) - 1