ADO.NET timestamp precision changes

Detail name	Value
Changelog Number	22146
Type	New Feature
Status	Resolved
Fix Versions	ADO.NET 24.2.0
Resolution Date	2024-11-11

Background

Starting with the Exasol ADO.NET driver 24.2.0 we support variable timestamp precision. To use this you need a database server that supports this features too. The default timestamp precision in the database stays unchanged, it is 3. The default session timestamp format remains unchanged, it is YYYY-MM-DD HH:MI:SS.FF6.

New Feature

For a better understanding of the differences to before, we will compare the outputs from a previous database version with the new version.
Changes in the metadata schema “Columns” for the columns of this table created with default session settings:

create or replace table timestamps_old(ts timestamp, ts_utc timestamp with local time zone)

Only the changed columns will be shown, first the previous value, the new one, changes apply to data types TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE.

Name of the column from the schema “Columns”	Value from the previous Exasol database (old)	Value from the 8.32.0 Exasol database (new)
COLUMN_SIZE	29	23
NUMERIC_PRECISION	29	null
NUMERIC_SCALE	9	null
DATETIME_PRECISION	null	3

Changes for the metadata schema “DataTypes”, data type TIMESTAMP:

Name of the column from the schema “DataTypes”	Value from the previous Exasol database (old)	Value from the 8.32.0 Exasol database (new)
CreateFormat	TIMESTAMP	TIMESTAMP( {0})| |CreateParameters|null|precision| Changes for the metadata schema “DataTypes”, data type TIMESTAMP WITH LOCAL TIMEZONE: ||Name of the column from the schema “DataTypes”||Value from the previous Exasol database (old)||Value from the 8.32.0 Exasol database (new)|| |CreateFormat|TIMESTAMP WITH LOCAL TIME ZONE|TIMESTAMP WITH LOCAL TIME ZONE({0}
CreateParameters	null	precision

)

Another change is in the way timestamps read from the new database are displayed as strings. This will now reflect the database servers setting for timestamp precision. For this example table:

create or replace table timestamps_new(
ts0 timestamp(0)
, ts1 timestamp(1)
, ts2 timestamp(2)
, ts3 timestamp(3)
, ts4 timestamp(4)
, ts5 timestamp(5)
, ts6 timestamp(6)
, ts7 timestamp(7)
, ts8 timestamp(8)
, ts9 timestamp(9)
);

insert into timestamps_new values(
'2024-10-17 16:22:39'
, '2024-10-17 16:22:39.1'
, '2024-10-17 16:22:39.12'
, '2024-10-17 16:22:39.123'
, '2024-10-17 16:22:39.1234'
, '2024-10-17 16:22:39.12345'
, '2024-10-17 16:22:39.123456'
, '2024-10-17 16:22:39.1234567'
, '2024-10-17 16:22:39.12345678'
, '2024-10-17 16:22:39.123456789'
);

The GetString() from the result will differ depending on the sessions setting. The insert was executed in the example with the default session timestamp format.
For an example alter session set nls_timestamp_format='YYYY-MM-DD HH:MI:SS.FF2 will lead to the output:

2024-10-17 16:22:39.00
, 2024-10-17 16:22:39.10
, 2024-10-17 16:22:39.12
, 2024-10-17 16:22:39.12
, 2024-10-17 16:22:39.12
, 2024-10-17 16:22:39.12
, 2024-10-17 16:22:39.12
, 2024-10-17 16:22:39.12
, 2024-10-17 16:22:39.12
, 2024-10-17 16:22:39.12

and alter session set nls_timestamp_format='YYYY-MM-DD HH:MI:SS.FF7 will lead to the output:

2024-10-17 16:22:39.0000000
, 2024-10-17 16:22:39.1000000
, 2024-10-17 16:22:39.1200000
, 2024-10-17 16:22:39.1230000
, 2024-10-17 16:22:39.1234000
, 2024-10-17 16:22:39.1234500
, 2024-10-17 16:22:39.1234560
, 2024-10-17 16:22:39.1234567
, 2024-10-17 16:22:39.1234567
, 2024-10-17 16:22:39.1234567

When displaying that value, the driver padded with “0” to the size of the format set in the last alter session statement, until it reached the size of 7 positions.

Minimum Exasol database server version required: 8.32.0

Changed behavior

See changelog description to learn about the changes in timestamp precision for Exasol ADO.NET driver