raminqaf commented on PR #27757: URL: https://github.com/apache/flink/pull/27757#issuecomment-4066513004
> @raminqaf I notice [PR 27770](https://github.com/apache/flink/pull/27770) is involved with precision and the code seems to round to 3 6 or 9 , where as your code deals with the other precisions like 7. Is this consistent? @davidradl Thanks for taking a look and raising this! Yes, supporting all intermediate precisions (1, 2, 4, 5, 7, 8) is actually the correct and standard behavior for timestamp types, even if other parts of the system or specific functions currently default to powers of 1000 (3, 6, 9). In most major databases, the TIMESTAMP and TIMESTAMP_LTZ data types fully support the entire spectrum of intermediate integers up to their maximum resolution. Restricting precision strictly to 3 (milliseconds), 6 (microseconds), or 9 (nanoseconds) is actually an artificial limitation. Here is how other major databases handle this, which FlinkSQL aligns with by supporting 0-9: - [Snowflake](https://docs.snowflake.com/en/sql-reference/data-types-datetime#timestamp) (TIMESTAMP_LTZ): Fully supports any integer precision from 0 to 9. Intermediate precisions like 7 will exactly truncate/round to 7 decimal places. - [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-7690645A-0EE3-46CA-90DE-C96DF5A01F8F) (TIMESTAMP WITH LOCAL TIME ZONE): Accepts any fractional seconds precision from 0 to 9. It natively enforces intermediate precisions perfectly. - [PostgreSQL](https://www.postgresql.org/docs/current/datatype-datetime.html) (TIMESTAMPTZ): Supports any integer from 0 to 6 (Postgres maxes out at microseconds). Specifying a precision of 1, 2, 4, or 5 is completely valid and strictly enforced. - [MySQL](https://dev.mysql.com/doc/refman/8.4/en/fractional-seconds.html) (TIMESTAMP): Similar to Postgres, allows any fractional precision from 0 to 6. By supporting 0-9 here, we bring Flink completely in line with standard ANSI SQL database behavior! Let me know what you think. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
