Year(MyDateField) <= YEAR(NOW()) not supported

[FreCap]

Is there any way to get the current year?

YEAR(NOW()) 

seems currently not supported

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "Nested function calls like [YEAR(NOW())] are not supported yet",
    "type": "SqlFeatureNotImplementedException"
  },
  "status": 503
}

Version: Version 1.13.0.0

[dai-chen]

Thanks for reporting the issue! Investigating. Are you using the latest ODFE version?

[FreCap]

Yes, latest version

[dai-chen]

It seems function NOW() is missing in new engine. It is supported by old engine by fallback but unfortunately nested function call and SELECT without FROM are not. Need to add it to the new engine: https://github.com/opendistro-for-elasticsearch/sql/blob/develop/docs/user/dql/functions.rst#now

[dai-chen]

Reference to MySQL and PostgreSQL spec:

1. MySQL's NOW(): "returns a constant time that indicates the time at which the statement began to execute ... The value is expressed in the session time zone ..."
2. PostgreSQL's NOW(): "returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client) ... deliver values with time zone ..."