Oracle SQL Developer 21.4: Configuring TIMESTAMP WITH LOCAL TIME ZONE Display Across Time Zones

Question

GoalDetermine the optimal strategy for rendering TIMESTAMP WITH LOCAL TIME ZONE values in SQL Developer when users operate in different regional settings.ConstraintsDATE columns lack time‑zone offsets; display relies on session NLS_DATE_FORMAT and JDBC defaults.On insert, TIMESTAMP WITH LOCAL TIME ZONE is normalized to the database time zone and returned adjusted to the session time zone.SQL Developer’s NLS preferences can change formats but do not alter the JDBC session time zone for all connection types.Unresolved DecisionShould the client render TIMESTAMP WITH LOCAL TIME ZONE values in the operating‑system local time or strictly follow the database session time zone? The current behavior follows the session time zone, which may misalign with user expectations in multi‑region deployments.Specific QuestionsWhich SQL Developer configuration setting, if any, can enforce rendering in the client OS local time for TIMESTAMP WITH LOCAL TIME ZONE columns?Does the JDBC thin driver in 21.4 expose a property to override the session time zone used by SQL Developer?What are the observable differences in query results when switching between a thick OCI connection and a thin JDBC connection in terms of time‑zone handling?

Wasi · Answer

Answers
1. SQL Developer setting to use client OS time
In SQL Developer 21.4 open Tools → Preferences → Database → NLS and set the Time Zone option to Client OS. This initializes the session SESSIONTIMEZONE to the operating‑system zone for the current connection, causing TIMESTAMP WITH LOCAL TIME ZONE values to be returned already adjusted to that zone.
2. JDBC thin driver override
The thin driver accepts the URL property oracle.jdbc.defaultNlsTimezone. When specified (e.g., ?oracle.jdbc.defaultNlsTimezone=UTC or SYSTEM), the driver sets SESSIONTIMEZONE to that value before any SQL is executed, thereby influencing how TIMESTAMP WITH LOCAL TIME ZONE columns are rendered.
3. Thick OCI vs. thin JDBC differences
Thick OCI connections inherit the client OS time zone via the Oracle client environment. Thin JDBC connections default to the database time zone unless oracle.jdbc.defaultNlsTimezone is supplied. Consequently, the same stored TIMESTAMP WITH LOCAL TIME ZONE value can appear with different offsets when queried through OCI versus thin.
Missing diagnostic detail
To tailor the recommendation, please confirm whether your SQL Developer connections use the thin JDBC driver or a thick OCI driver.

Oracle SQL Developer 21.4: Configuring TIMESTAMP WITH LOCAL TIME ZONE Display Across Time Zones

Goal

Constraints

Unresolved Decision

Specific Questions

1 answer

Answers

1. SQL Developer setting to use client OS time

2. JDBC thin driver override

3. Thick OCI vs. thin JDBC differences

Missing diagnostic detail

Your perspective belongs here.

0 question comments