This is a generic ask for more flexibility when dealing with timestamps that have a different format than the one Elasticsearch is expecting to get in queries.
Because ES SQL doesn't have a way to find the actual format a date field is being configured with in Elasticsearch,when creating a query the user needs to have this knowledge and provide the proper text string when dealing with timestamps. For example SELECT timestamp FROM test WHERE timestamp = '1990-12-12 10:23:00' will fail in a default index configuration in Elasticsearch because ES will expect the date format to be 1990-12-12T10:23:00.
With a function like to_char, the query above becomes SELECT timestamp FROM test WHERE to_char(timestamp, 'yyyy-MM-dd HH:mm:ss') = '1990-12-12 10:23:00'.
Using custom date formats could also work the other way around with functions like to_date(text, format) or to_timestamp.
Relates to #50193.
This is a generic ask for more flexibility when dealing with timestamps that have a different format than the one Elasticsearch is expecting to get in queries.
Because ES SQL doesn't have a way to find the actual format a
datefield is being configured with in Elasticsearch,when creating a query the user needs to have this knowledge and provide the proper text string when dealing with timestamps. For exampleSELECT timestamp FROM test WHERE timestamp = '1990-12-12 10:23:00'will fail in a default index configuration in Elasticsearch because ES will expect the date format to be1990-12-12T10:23:00.With a function like
to_char, the query above becomesSELECT timestamp FROM test WHERE to_char(timestamp, 'yyyy-MM-dd HH:mm:ss') = '1990-12-12 10:23:00'.Using custom date formats could also work the other way around with functions like
to_date(text, format)orto_timestamp.Relates to #50193.