Extended Timestamp Fractional Seconds Precision

Details

Detail name Value
Changelog Number 13409
Type Improvement
Status Resolved
Fix Versions Exasol 8.32.0
Resolution Date 2024-11-08

Background

Timestamps can contain fractions of a second. For example, the timestamp '2024-01-01 00:00:00.123456789' contains nine fractional digits. In the past, Exasol did not support specifying the fractional seconds precision for the data types TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE but always used three fractional digits which equates to millisecond precision.

Improvement

Data Types

The timestamp data types now allow specifying the fractional seconds precision up to nine fractional digits which equates to nanosecond precision:

  • TIMESTAMP(p)
  • TIMESTAMP(p) WITH LOCAL TIME ZONE

where p is between 0 and 9.

Specifying the precision is optional and the default precision remains three fractional digits, see CHANGELOG: TIMESTAMP as alias for TIMESTAMP(3).

Timestamp Format

In format models the fractional seconds precision can be specified by using FF1 through FF9. This was already supported by previous Exasol versions. The default format model for the session/system parameter NLS_TIMESTAMP_FORMAT remains unchanged: YYYY-MM-DD HH24:MI:SS.FF6

System Tables

In the tables EXA_ALL_COLUMNS, EXA_DBA_COLUMNS, EXA_USER_COLUMNS, and EXA_SYS_COLUMNS, the column COLUMN_NUM_PREC now contains the fractional seconds precision for timestamp types instead of NULL. In the table EXA_SQL_TYPES, the column CREATE_PARAMS now contains the string precision for timestamp types.

Clients

EXAplus must be updated to version 24.2.0 to use the improved timestamp data types.

Drivers

The drivers must be updated to the following versions to use the improved timestamp data types:

WebSocket API

The new WebSocket API v5 includes the fractional seconds precision in responses sent by the database to the client. For the data types TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE the new property precision was added, see WebSocket protocol v5 API specification. Please notice, that timestamp values are transferred as strings using the format from the session parameter NLS_TIMESTAMP_FORMAT which might need to be changed to get the full timestamp value.

Virtual Schema API

The Virtual Schema API was extended by a new attribute "fractionalSecondsPrecision" for the TIMESTAMP data type, see https://github.com/exasol/virtual-schema-common-java/blob/main/doc/development/api/data_types_api.md#timestamp.. Custom adapters might also have to adapt their handling of literal_timestamp and literal_timestamputc to handle different numbers of fractional digits correctly.

Script Language Container API

For timestamp types, the list of input/output column types sent to the SLC now contains the fractional seconds precision instead of zero in the precision field. Timestamp output values sent by the SLC can now contain up to nine fractional digits.

Restrictions

  • The related data type INTERVAL DAY TO SECOND still stores at most three fractional digits for the seconds, even if more are specified.
  • Existing virtual schema adapters maintained by Exasol don’t support the extended API yet, so the usage of virtual schemas is still limited to TIMESTAMP(3) until extended adapters are released.
  • UDF scripts support the TIMESTAMP precision in input/output columns and metadata, but there are some restrictions:
  • For TIMESTAMP(7) to TIMESTAMP(9) input columns, all input values are truncated to six fractional digits, making them behave like TIMESTAMP(6). A future database version will remove this restriction for the languages Java and R.
  • For the language Python 3 the internal data type datetime.datetime only supports six fractional digits, so input and output values are also limited to six fractional digits, even if TIMESTAMP(7) to TIMESTAMP(9) are used.
  • For the language R all output values are truncated to three fractional digits, even if TIMESTAMP(4) to TIMESTAMP(9) are used. A future database version will remove this restriction.
  • For the input/output column metadata which can be queried within UDF scripts through the exa object, the SQL type name correctly shows the TIMESTAMP type including the fractional seconds precision, but the separate precision field is empty (e.g., exa.getInputColumnPrecision() in Java). A future database version will fix this.

Common Problems

The default setting for the session/system parameter NLS_TIMESTAMP_FORMAT remains unchanged for compatibility and thus still contains FF6, i.e., six fractional digits. This format is used for implicit casts of timestamps from/to strings. This default format can cause confusion when the data types TIMESTAMP(7) to TIMESTAMP(9) are used because all implicit casts use this parameter and thus truncate timestamps to six fractional digits. For example INSERT INTO timestamp_9_table VALUES ('2000-01-01 00:00:00.123456789') inserts the value TIMESTAMP '2000-01-01 00:00:00.123456000' (notice the three different fractional digits). And SELECT TIMESTAMP '2000-01-01 00:00:00.123456789' = '2000-01-01 00:00:00.123456789' returns FALSE because the right-hand side is a string which is cast to TIMESTAMP '2000-01-01 00:00:00.123456000'. To alleviate this, we recommend to use timestamp literals instead of string literals and change the default timestamp format to FF9.

Changed Behavior

  • The type of timestamp literals now depends on the number of fractional digits given in the literal. For example, TIMESTAMP '2024-01-01 00:00:00.1234' has the data type TIMESTAMP(4), and TIMESTAMP '2024-01-01 00:00:00.000000000' has the type TIMESTAMP(9). This also affects the comparison with timestamp literals, e.g., TIMESTAMP '2024-01-01 00:00:00.123456789' is no longer equal to TIMESTAMP '2024-01-01 00:00:00.123'.
  • Expressions and functions which combine values of different types now choose a different return type in some cases. This affects all table operators (UNION, UNION ALL, INTERSECT, EXCEPT/MINUS), the CASE expression, and the functions COALESCE, DECODE, GREATEST, LEAST, NVL, and NVL2.
  • The functions TO_TIMESTAMP and CONVERT_TZ now return TIMESTAMP(9) if the first parameter is a string or TIMESTAMP(0) if the first parameter is a DATE. Previously these functions always returned TIMESTAMP(3).
  • The functions TO_TIMESTAMP(arg, format), TO_DATE(arg, format), IS_TIMESTAMP(arg, format), IS_DATE(arg, format), and IS_NUMBER(arg, format) now completely ignore the format parameter if the first argument is not a string and thus no string format is needed.
  • The return type of CURRENT_TIMESTAMP(p) changed from TIMESTAMP(3) WITH LOCAL TIME ZONE to TIMESTAMP(p) WITH LOCAL TIME ZONE. The returned value is limited to microsecond precision, even if p is greater than 6. The function now yields an error if p is greater than 9.
  • The return type of LOCALTIMESTAMP(p) changed from TIMESTAMP(3) WITH LOCAL TIME ZONE to TIMESTAMP(p) (no longer WITH LOCAL TIME ZONE). The returned value is limited to microsecond precision, even if p is greater than 6. The function now yields an error if p is greater than 9.
  • The return type of SYSTIMESTAMP(p) changed from TIMESTAMP(3) to TIMESTAMP(p). The returned value is limited to microsecond precision, even if p is greater than 6. The function now yields an error if p is greater than 9.
  • The numeric argument of the function ADD_SECONDS(timestamp, numeric) is now truncated instead of rounded to the fractional seconds precision of the timestamp argument.
  • The result of the function PERCENTILE_CONT for TIMESTAMP(3) parameters is reduced by one millisecond for some input values, caused by different rounding.
  • If the IMPORT statement is used without INTO, the data types of the resulting columns are determined based on the data source column types. If the source type is a timestamp type, the result type is now chosen differently:
  • CURRENT_DATE and CURRENT_TIMESTAMP must name columns, if they are not in the outer query.
  • For a mix of TIMESTAMP(p) [WITH LOCAL TIME ZONE] values with different fractional seconds precisions p, the return type uses the greatest value of p as the fractional seconds precision.
  • For a mix of TIMESTAMP(p) and TIMESTAMP(p) WITH LOCAL TIME ZONE values, the return type is now TIMESTAMP(p) WITH LOCAL TIME ZONE instead of TIMESTAMP(p).
  • For a mix of TIMESTAMP(p) [WITH LOCAL TIME ZONE] and DATE values, the return type is now TIMESTAMP(p) [WITH LOCAL TIME ZONE] instead of DATE.
  • For a mix of TIMESTAMP(p) [WITH LOCAL TIME ZONE] and string values, the return type is now TIMESTAMP(9) [WITH LOCAL TIME ZONE] instead of TIMESTAMP(p) [WITH LOCAL TIME ZONE].
  • For IMPORT FROM EXA, the result type matches the source type instead of always being TIMESTAMP(3) or TIMESTAMP(3) WITH LOCAL TIME ZONE.
  • For IMPORT FROM JDBC, the result type is now always TIMESTAMP(9) instead of TIMESTAMP(3).
  • For IMPORT FROM ORA, the result type is now always TIMESTAMP(9) or TIMESTAMP(9) WITH LOCAL TIME ZONE instead of TIMESTAMP(3) or TIMESTAMP(3) WITH LOCAL TIME ZONE, respectively.
  • with cte as ( select current_date ) select * from cte throws "must name expression in query CTE with a column alias"
  • with cte as ( select current_timestamp ) select * from cte throws "must name expression in query CTE with a column alias"

Changed behavior

Many changes related to the TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE data types and date/time functions. Please see changelog entry.