Versions Compared

Old Version 21

changes.mady.by.user Leonard Xu

Saved on

compared with

New Version 22

changes.mady.by.user Leonard Xu

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

These cast conversions have wrong behavior and problematic semantics,  because SQL:2011 does not contains these cast specification and we never expose to user.

This FLIP propose to disable the cast between numeric and timestamp,  and introduce a the function proposed use current function TO_TIMESTAMP(seconds) function which accepts a BIGINT seconds and return a TIMESTAMP in local time zoneFROM_UNIXTIMESTAMP(epochSeconds - zoneOffset)) as a migration plan. 

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_UNIXTIMESTAMP(44 - 0))

1970-01-01 00:00:44 

#session timezone: UTC+8

TO_TIMESTAMP(FROM_UNIXTIMESTAMP(

44 

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

...