Comparison between "Timestamp with local time zone" and "Date" datatypes ignores time zone
Details
| Detail name | Value |
|---|---|
| Changelog Number | 15053 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | Exasol 6.2.0, Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0 |
| Fix Versions | Exasol 8.0.6, Exasol 7.1.12, Exasol 7.0.20 |
| Resolution Date | 2022-07-13 |
Background
Exasol supports the data type TIMESTAMP WITH LOCAL TIME ZONE. When a user enters a timestamp of this type, the timestamp is interpreted in the local session time zone, but converted and stored as UTC internally. When the timestamp is queried and displayed again it gets converted to the local session time zone again, which might be a different time zone. For more details, see the documentation.
Description
A comparison between TIMESTAMP WITH LOCAL TIME ZONE and DATE incorrectly uses UTC instead of the local time zone during the comparison.
This affects the equality operator = as well as less than, greater than etc.
Workaround
Cast DATE values to TIMESTAMP before comparison.
Fix
A comparison between TIMESTAMP WITH LOCAL TIME ZONE and DATE uses the local time zone during the comparison.
