SQL: Custom timezone is not used in CAST/CONVERT

[matriv]

SELECT CAST('2019-01-14T12:29:25.000+05:00' AS DATETIME) returns
2019-01-14T12:29:25.000Z` instead of `+05:00

Also the whole CASTING/ESCAPE literals should be revisited as UTC is always used.
So if the client uses a custom timezone and uses a filter like: SELECT * FROM t WHERE created = CAST('2019-01-14T12:29:25.000' AS DATETIME) the date literal will be in UTC which I think is not what the client would expect.

Also simple SELECT CAST('2019-01-14T12:29:25.000' AS DATETIME) should convert it to the timezone set by the client and not UTC.

[elasticmachine]

Pinging @elastic/es-search

[matriv]

@costin @astefan Please share your opinion for the 2nd part of the bug (as the 1st seems straightforward?)

[astefan]

@matriv I am not convinced about the first part. I've tested PostgreSQL and the timezone is not outputted for something like SELECT CAST('2019-02-14T12:29:25.000+07:00' AS TIMESTAMP);. But I kinda agree on the second part.

[matriv]

@astefan But you cast a well defined timestamp at a custom timezone and you get back the same exact timestamp but with indication UTC.

[matriv]

@elastic/es-ql

[Luegg]

Looks like the bug has been resolved in the meantime. I cannot reproduce it on 7.14.0 anymore:

SELECT CAST('2019-02-14T00:00:00.000+05:00' AS TIMESTAMP) ts

           ts
------------------------
2019-02-13T19:00:00.000Z

So the timezone has been considered but the result is still presented in UTC and not in the time zone provided with the time_zone parameter.

[Luegg]

The second part is still present though:

curl --silent --show-error --include -H 'Content-Type: application/json' \
-X POST 'http://localhost:9201/_sql?format=txt' \
-d '{"query": "select '\''1987-09-21T00:00:00'\''::datetime dt", "time_zone":"+05:00"}'

           dt
------------------------
1987-09-21T00:00:00.000Z

But the output should probably be equivalent to specifying the time zone in the query:

curl --silent --show-error --include -H 'Content-Type: application/json' \
-X POST 'http://localhost:9201/_sql?format=txt' \
-d '{"query": "select '\''1987-09-21T00:00:00+05:00'\''::datetime dt"}'

           dt
------------------------
1987-09-20T19:00:00.000Z

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)