THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
Status
Current state: under discussion
...
Please keep the discussion on the mailing list rather than commenting on the wiki (wiki discussions get unwieldy fast).
Motivation
Currently some temporal function behaviors are wired to users.
...
This FLIP aims to consistent the timestamp function behavior and eventually improve the usability.
Public Interfaces
As we knew some functions' behavior is wrong currently, but after we correct these function, the legacy behavior should still work in old code.
...
And when the option also influence the result of CAST conversion between NUMERIC TYPE and TIMESTAMP, by default，the CAST conversion is not supported anymore, users can use TO_TIMESTAMP(milliseconds) function to get a TIMESTAMP in local time zone from a java epoch milliseconds. Users can set the option to 'true', thus these cast conversion would keep the legacy behavior.
Proposed Changes
1. Correct five time function behavior
I invested all Flink time-related functions current behavior and compared with other DB vendors like Pg,Presto, Hive, Spark, Snowflake, I made an excel [2] to organize them well.
To correct our current behavior, we need to make the function return type clear, especially for timestamp type, i.e.
- TIMESTAMP/TIMESTAMP WITHOUT TIME ZONE
- TIMESTAMP WITH LOCAL TIME ZONE
- TIMESTAMP WITH TIME ZONE
...
In order to understand these types better, I wrote a document[3]. BTW, Flink also keeps same semantics for three timestamp types compareing comparing with Hadoop ecosystem.
From my investigation, to correct this time functions' behavior, we have server serveral options
(1) change the function return type
(2) change the function return value
(3) change them both.
All of those way are valid because SQL:2011 does not specify the function return type and every SQL engine vendor has its own implementation[2], for exampe example CURRENT_TIMESTAMP.
| function | flink current behavior | flink proposed changes | other SQL vendors' behavior |
| CURRENT_TIMESTAMP | #session timezone: UTC 2020-12-28T23:52:52 #session timezone: UTC+8 2020-12-28T23:52:52 wall clock: UTC+8:2020-12-29 07:52:52 | #session timezone: UTC 2020-12-28T23:52:52 #session timezone: UTC+8 2020-12-29T07:52:52 | In MySQL, Spark, the function NOW() and CURRENT_TIMESTAMP return current timestamp value in session time zone，the return type is TIMESTAMP In Pg, Presto, the function NOW() and CURRENT_TIMESTAMP return current timestamp in session time zone，the return type is TIMESTAMP WITH TIME ZONE In Snowflake the function CURRENT_TIMESTAMP/LOCALTIMESTAMP return current timestamp in session time zone，the return type is TIMESTAMP WITH LOCAL TIME ZONE |
This FLIP proposed option(2) which only change the return value,Correct time-related function behavior the proposed changes as following, assume the current wall-clock is 2020-12-29 07:52:52 in Beijing time(UTC+8).:
function | existed problem | current behavior | proposed changes |
CURRENT_DATE | returns UTC date, but user expects current date in session time zone | return type: DATE #session timezone: UTC 2020-12-28 #session timezone: UTC+8 2020-12-28 | return current date in session time zone, the return type should be DATE #session timezone: UTC 2020-12-28 #session timezone: UTC+8 2020-12-29 |
CURRENT_TIME | returns UTC time, but user expects current time in session time zone | return type: TIME #session timezone: UTC 23:52:52 #session timezone: UTC+8 23:52:52 | return current time in session time zone, the return type should be TIME #session timezone: UTC 23:52:52 #session timezone: UTC+8 07:52:52 |
CURRENT_TIMESTAMP | returns UTC timestamp, but user expects current timestamp in session time zone | return type: TIMESTAMP #session timezone: UTC 2020-12-28T23:52:52 #session timezone: UTC+8 2020-12-28T23:52:52 | return current timestamp in session time zone, the return type should be TIMESTAMP #session timezone: UTC 2020-12-28T23:52:52 #session timezone: UTC+8 2020-12-29T07:52:52 |
NOW() | returns UTC timestamp, but user expects current timestamp in session time zone | return type: TIMESTAMP #session timezone: UTC 2020-12-28T23:52:52 #session timezone: UTC+8 2020-12-28T23:52:52 | return current timestamp in session time zone, the return type should be TIMESTAMP #session timezone: UTC 2020-12-28T23:52:52 #session timezone: UTC+8 2020-12-29T07:52:52 |
PROCTIME() | returns UTC timestamp, but user expects current timestamp in session time zone | return type: TIMESTAMP *PROCTIME* #session timezone: UTC 2020-12-28T23:52:52 #session timezone: UTC+8 2020-12-28T23:52:52 | return current timestamp in session time zone for PROCTIME(), the return type should be TIMESTAMP *PROCTIME* #session timezone: UTC 2020-12-28T23:52:52 #session timezone: UTC+8 2020-12-29T07:52:52 |
...
After the proposal, the function NOW(), CURRENT_TIMESTAMP and LOCALTIMESTAMP become synonyms, the function CURRENT_TIME and LOCALTIME become synonyms, you can also lookup all time function behaviors in reference [2].
2. Correct CAST conversion between NUMERIC TYPE and TIMESTAMP type
Currently, the following CAST conversion behaviors are wrong which does not consider the session time zone. It should use session time zone when cast between NUMERIC type and TIMESTAMP type if there’re strong requirements to support this , the numeric type include TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, they keep same conversion behavior, for example the cast between BIGINT and TIMESTAMP. Although we didn’t expose this feature in the document, some users may use them.
As these These cast conversions have wrong behavior and problematic semantics, because SQL:2011 does not contains these cast specification and we never expose to user, a better way to replace these functionalities is the TO_TIMESTAMP(millisecondsseconds) function.
function | current behavior | existed problem | proposed changes |
CAST(44 AS TIMESTAMP) | TIMESTAMP(6) NOT NULL #session timezone: UTC 1970-01- |
01T00:00:44 #session timezone: UTC+8 1970-01- |
01T00: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 |
Support function TO_TIMESTAMP( |
seconds) |
return type: TIMESTAMP( |
3) #session timezone: UTC TO_TIMESTAMP(44) 1970-01-01T00:00: |
44 #session timezone: UTC+8 TO_TIMESTAMP(-28756) 1970-01-01T |
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 |
General Implementations
1.Change the codegen implementations for above five functions/cast conversions according to the value of introduced table option: table.exec.fallback-legacy-time-function.enable
2.Consider the session time zone offset when materializing a PROCTIME() attribute, including PROCTIME_MATERIALIZE function and the processing timestamp used to register timer in the window operator, because after change the return value of PROCTIME(）function,
the window based on processing time should be triggered by the changed processing time value.
3. When converting a Table contains rowtime from/to DataStream, the timestamp in StreamRecord will exchange with Table’s row time column. We need to consider the time zone offset just like the cast conversion between the TIMESTAMP and BIGINT. This way can make the
both DataStream and SQL/API users feel intuitive.
Compatibility, Deprecation, and Migration Plan
- Compatibility
This is an incompatible change, we introduce SQL/API option table.exec.fallback-legacy-time-function for compacting current wrong behavior, and set it to ‘false’. If users want to keep the legacy behavior, they need to set it to ’true’ manually, this would be add to release note.
- Migration Plan
After the proposal is finished, the above user cases will work smoothly. Assume users' local time zone is UTC+8, the wall-clock is 2020-12-29 07:52:52.
...
records earlier than 2020-12-29 will not be output.
General Implementations
1.Change the codegen implementations for above five functions/cast conversions according to the value of introduced table option: table.exec.fallback-legacy-time-function.enable
2.Consider the session time zone offset when materializing a PROCTIME() attribute, including PROCTIME_MATERIALIZE function and the processing timestamp used to register timer in the window operator, because after change the return value of PROCTIME(）function,
the window based on processing time should be triggered by the changed processing time value.
3. When converting a Table contains rowtime from/to DataStream, the timestamp in StreamRecord will exchange with Table’s row time column. We need to consider the time zone offset just like the cast conversion between the TIMESTAMP and BIGINT. This way can make the
both datastream and SQL/API users feel intuitive.
...
- Compatibility
This is an incompatible change, we introduce SQL/API option table.exec.fallback-legacy-time-function for compacting current wrong behavior, and set it to ‘false’. If users want to keep the legacy behavior, they need to set it to ’true’ manually.
- Deprecation
- Migration Plan
Test Plan
Will add plan tests, unit tests, window operator harness tests as well as IT tests.
Rejected Alternatives
(1) Change . Change the return type of function CURRENT_TIMESTAMP/NOW()/PROCETIME() to TIMESTAMP WITH TIME ZONE. This proposal needs to introduce a new type TIMESTAMP WITH TIME ZONE, and we think there are no enough benefits. If we do this, the return type of function CURRENT_TIME must be TIME WITH TIME ZONE for consistent consideration, we need to introduce another type.
（2）Change 2. Change the return type of function CURRENT_TIMESTAMP/NOW()/PROCETIME() to TIMESTAMP WITH LOCAL TIME ZONE. This proposal will lead to a embarrassed situation for function CURRENT_TIME, because no DB vendor use type TIME WITH LOCAL TIME ZONE yet, and the time semantic of TIMESTAMP WITH LOCAL TIME ZONE is instant which is too complex to understand for normal users.
References:
...