[BUG] OpenSearch does not support timestamp format {ts 'YYYY-MM-DD hh:mm:ss'}

[guiangumpac]

Describe the bug OpenSearch does not support timestamp format {ts 'YYYY-MM-DD hh:mm:ss'} which results in date filters on PowerBI to fail.

To Reproduce Or for query issues:

1. Start OpenSearch server
2. Run the query 'SELECT {ts '1975-11-12 00:00:00'}'
3. See error 'Invalid SQL query'

Expected behavior This query should have the same result as TIMESTAMP('1975-11-12 00:00:00')

Screenshots

Additional context Used OpenSearch version 1.2.0

[dai-chen]

The date format is supported actually. The problem seems in TS syntax is missing:

curl -XPOST "localhost:9200/_plugins/_sql" -H 'Content-Type: application/json' -d '
{
  "query": "SELECT timestamp('\''1975-11-12 00:00:00'\'') "
}
'
{
  "schema": [
    {
      "name": "timestamp('1975-11-12 00:00:00')",
      "type": "timestamp"
    }
  ],
  "datarows": [
    [
      "1975-11-12 00:00:00"
    ]
  ],
  "total": 1,
  "size": 1,
  "status": 200
}

[Yury-Fridlyand]

Should we add it?

[dai-chen]

I think so. This is supported in legacy engine only. For some reason, we didn't enable it in v2 and it always fallback to legacy engine:

• https://github.com/opensearch-project/sql/blob/main/legacy/src/main/antlr/OpenSearchLegacySqlParser.g4#L280
• https://github.com/opensearch-project/sql/blob/main/sql/src/main/antlr/OpenSearchSQLParser.g4#L195

[acarbonetto]

note: also support {d }, {dt }, and {t } formats from ODFE driver