Page History

function

current behavior

existed problem

migration plan

CAST(44 AS TIMESTAMP) 

TIMESTAMP(0) NOT NULL

#session timezone: UTC

1970-01-01 00:00:44 

#session timezone: UTC+8

1970-01-01 00:00:44 

The time in BIGINT type usually represents a unixtime semantic, which represents the elapsed time since java epoch(1970-01-01 00:00:00 UTC+0), when convert to a timestamp we should consider local time zone

This is an invalid behavior, disable the invalid CAST behavior, to get same behavior, user can workaround with: 

#session timezone: UTC

• TO_TIMESTAMP(FROM_UNIXTIME(44 - 0))

1970-01-01 00:00:44 

#session timezone: UTC+8

TO_TIMESTAMP(FROM_UNIXTIME(44 - 8 * 60 * 60))

1970-01-01 00:00:44

CAST(TIMESTAMP ‘1970-01-01 00:00:44’ AS BIGINT) 

BIGINT NOT NULL

#session timezone: UTC

44 

#session timezone: UTC+8

44

The inverse conversion of above, this conversion is used rarely.

UNIX_TIMESTAMP(TIMESTAMP ‘1970-01-01 00:00:44’)

#session timezone: UTC

44

#session timezone: UTC+8

-28756

proposed changes

Support function  TO_TIMESTAMP_LTZ(numeric_expr, [,scale])

return type: TIMESTAMP(3) WITH LOCAL TIME ZONE

#session timezone: UTC

TO_TIMESTAMP_LTZ(44)

1970-01-01 00:00:44 

#session timezone: UTC+8

TO_TIMESTAMP_LTZ(-28756)

1970-01-01 00:00:44

TO_TIMESTAMP_LTZ(numeric_expr [,scale])

TO_TIMESTAMP_LTZ(seconds, 0)

TO_TIMESTAMP_LTZ(milliseconds, 3)