Wrong results for CAST from TIMESTAMP WITH LOCAL TIME ZONE to CHAR
Details
| Detail name | Value |
|---|---|
| Changelog Number | 17258 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0 |
| Fix Versions | Exasol 8.21.0 |
| Resolution Date | 2023-08-04 |
Description
If a query performs a CAST from a column of type TIMESTAMP WITH LOCAL TIME ZONE to a CHAR type, the cast returns a timestamp in the timezone UTC instead of the expected local time zone.
Preparation:
ALTER SESSION SET TIME_ZONE = 'AMERICA/CHICAGO'; -- UTC-5 CREATE OR REPLACE TABLE TEST.T1 (C1 TIMESTAMP WITH LOCAL TIME ZONE); INSERT INTO TEST.T1 VALUES (CURRENT_TIMESTAMP);
Example:
SELECT CAST(C1 AS CHAR(30)), CAST(C1 AS VARCHAR(200)) FROM TEST.T1;
returns
| CAST(T1.C1 AS CHAR(30) UTF8) | CAST(T1.C1 AS VARCHAR(200) UTF8) |
|---|---|
| 2023-08-04 16:41:46.250000 | 2023-08-04 11:41:46.250000 |
In the example above, the expected result for both columns is 2023-08-04 11:41:46.250000.
Workaround
Cast the column to VARCHAR instead of CHAR to return the correct result.
Fix
The CAST from TIMESTAMP WITH LOCAL TIME ZONE to CHAR now returns the correct result, as expected.
Changed behavior
The CAST from TIMESTAMP WITH LOCAL TIME ZONE to CHAR now returns the correct result.
