Hi Expert,
this document [1] said `TO_TIMESTAMP` will use the session time zone to convert date time string into a timestamp.
If I understand correctly, when I set session time zone to `Asia/Shanghai` and query `SELECT TO_TIMESTAMP('1970-01-01 08:00:00');`,
the result should be epoch timestamp `0` (i.e. '1970-01-01 08:00:00 UTC+8').
TO_TIMESTAMP(string1[, string2])
| Converts date time string string1 with format string2 (by default: 'yyyy-MM-dd HH:mm:ss') under the session time zone (specified by TableConfig) to a timestamp. Only supported in blink planner. |
However, I found that result is not same as I expected. I tested it by running the below query under the `Asia/Shanghai` timezone:
SELECT
CAST(TO_TIMESTAMP(FROM_UNIXTIME(0)) AS BIGINT),
FROM_UNIXTIME(0),
TO_TIMESTAMP(FROM_UNIXTIME(0));
and I got the result like
EXPR$0 EXPR$1 EXPR$2
28800 1970-01-01 08:00:00 1970-01-01T08:00
The `FROM_UNIXTIME` did convert the epoch timestamp to string format based on session time zone, but `FROM_UNIXTIME` didn't.
Therefore, I got the `28800` when I cast timestamp into bigint. The result actually shift 8 hours.
I found this code snippet [2] might be related to `TO_TIMESTAMP` udf, and seems like it won't set use any timezone configuration, so maybe the document might be wrong.
Please correct me if I misunderstood something. Thank you.
best regards,
Locked
