Incorrect return type for addition of TIMESTAMP WITH LOCAL TIME ZONE
Details
| Detail name | Value |
|---|---|
| Changelog Number | 13899 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0, Exasol 8.1.0, Exasol 8.2.0, Exasol 8.3.0 |
| Fix Versions | Exasol 8.6.0 |
| Resolution Date | 2022-10-12 |
Description
If the session parameter TIMESTAMP_ARITHMETIC_BEHAVIOR is set to 'DOUBLE', adding an integer to a TIMESTAMP WITH LOCAL TIME ZONE incorrectly results in a normal TIMESTAMP.
Example
ALTER SESSION SET TIME_ZONE = 'CET';
ALTER SESSION SET TIMESTAMP_ARITHMETIC_BEHAVIOR = 'DOUBLE';
SELECT typeof(CAST('2021-03-27 02:30:00' AS TIMESTAMP WITH LOCAL TIME ZONE) + 1);
Workaround
There is no workaround
Fix
The resulting data type of such an operation will be TIMESTAMP WITH LOCAL TIME ZONE.
The operation timestamp + x will be equivalent to ADD_SECONDS(timestamp, x * 24 * 3600).
