Discussion on date datatypes and adddate functionality.

[vmmusings]

@Yury-Fridlyand Can you help me with the below query.

####
POST http://localhost:9200/_plugins/_sql
content-type: application/json

{
  "query":"select adddate(cast(timestamp as date), INTERVAL 270 DAY)  from opensearch_dashboards_sample_data_logs where response=503 and adddate(cast(timestamp as date), INTERVAL 270 DAY) >= '2025-22-01'"
}

Result

HTTP/1.1 500 Internal Server Error
content-type: application/json; charset=UTF-8
content-encoding: gzip
content-length: 298

{
  "error": {
    "type": "SearchPhaseExecutionException",
    "reason": "Error occurred in OpenSearch engine: all shards failed",
    "details": "Shard[0]: org.opensearch.sql.exception.SemanticCheckException: datetime:2025-22-01 in unsupported format, please use yyyy-MM-dd HH:mm:ss[.SSSSSSSSS]\n\nFor more details, please send request for Json format to see the raw response from OpenSearch engine."
  },
  "status": 503
}

Questions:

• Although it is giving error, is this a valid query. Particularly this clause adddate(cast(timestamp as date), INTERVAL 270 DAY) >= '2025-22-01'
• From the documentation, it seems adddate(cast(timestamp as date), INTERVAL 270 DAY) return datetime object with interval. So this comparison is not possible as both of them are of different types.
• How should I make this query work?
  • cast(adddate(cast(timestamp as date), INTERVAL 270 DAY) as date) >= '2025-22-01' or
  • adddate(cast(timestamp as date), INTERVAL 270 DAY) >= DATETIME('2025-22-01')
• Also it seems the query doesn't throw any error if the prior where clause gives zero documents result set. For e.g. The above query throws error when I use response = 503 and not if I use response=45 as there are no documents related to response = 45. I guess this
• Are there any recent changes introduced compared to 1.3 version.

[acarbonetto]

Are there any recent changes introduced compared to 1.3 version.

If you're using version 1.3, you should consider switching to 2.x. #722 was just recently closed and will be released completed in 2.6. Also, adddate was updated in December.

Also, it looks like the date format for that field is yyyy-dd-MM HH:mm:ss instead of yyyy-MM-dd HH:mm:ss. The format field in the mapping isn't used until its fixed in #794.

[Yury-Fridlyand]

Use can use STR_TO_DATE function implemented recently in #1420 for any non-default date format.
Also response seems like string column, not a number.
Please, see updated query:

select adddate(cast(timestamp as date), INTERVAL 270 DAY)  from opensearch_dashboards_sample_data_logs where response="503" and adddate(cast(timestamp as date), INTERVAL 270 DAY) >= STR_TO_DATE('2025-22-01', '%Y-%m-%d');

or without STR_TO_DATE:

select adddate(cast(timestamp as date), INTERVAL 270 DAY)  from opensearch_dashboards_sample_data_logs where response="503" and adddate(cast(timestamp as date), INTERVAL 270 DAY) >= '2025-01-22';

[vmmusings]

Does the second query work? adddate(cast(timestamp as date), INTERVAL 270 DAY) >= '2025-01-22' doesn't it throw error as both of them are different datatypes?

[Yury-Fridlyand]

Oh, you're right, it should be

select adddate(cast(timestamp as date), INTERVAL 270 DAY)  from opensearch_dashboards_sample_data_logs where response="503" and adddate(cast(timestamp as date), INTERVAL 270 DAY) >= DATE '2025-01-22';

Why?
adddate returns DATETIME and FunctionResolver picks (DATETIME, DATETIME) -> DATETIME signature for >=. Then it adds cast STRING -> DATETIME for the second argument, and this cast fails.
If you set your second argument as DATE, resolver uses case DATE -> DATETIME which works fine.
This could be fixed in scope of #856.