SQL: conditions involving date fields with custom format and SQL-generated dates fail

[astefan]

For a date field with a custom format like HH:mm yyyy-MM-dd, a simple query like SELECT * FROM test WHERE date > NOW() will fail with an error:

        "root_cause": [
            {
                "type": "parse_exception",
                "reason": "failed to parse date field [2019-08-02T10:42:15.773Z] with format [HH:mm yyyy-MM-dd]: [Text '2019-08-02T10:42:15.773Z' could not be parsed at index 2]"
            }
        ],

Moreover, if a timezone is passed in the request in the {area}/{city} format (for REST - for example "time_zone": "Europe/Paris"), the date SQL generates and places it in the range filter also specifies the {area}/{city} format alongside the offset. The offset should be enough and the area/city is redundant.

        "root_cause": [
            {
                "type": "parse_exception",
                "reason": "failed to parse date field [2019-08-02T12:42:53.804+02:00[Europe/Paris]] with format [HH:mm yyyy-MM-dd]: [Text '2019-08-02T12:42:53.804+02:00[Europe/Paris]' could not be parsed at index 2]"
            }
        ],

[elasticmachine]

Pinging @elastic/es-search