Timezones in Oracle Database

Suppose that you're managing an application that provides the estimated delivery times for users. Your database resides in Los Angeles. Users worldwide: one of the users may place his/her orders with local TIMESTAMP values from, say New York, while another user does the same from Tokyo. In this scenario, it is necessary to keep records of user time zones; if your data is not time zone-aware, the estimated delivery time would be incorrect for users in different time zones.

Oracle Database supports time zone representations when dealing with TIMESTAMP data type. Here's a summary of key concepts of time zones in Oracle:

• Database Time Zone: The default time zone for the database, typically set to the server's location. It governs the default interpretation of TIMESTAMP stored in the database, if not specific time zone is provided.
• Session Time Zone: The time zone used for the current user session. It determines how timestamps are displayed and manipulated within the session and can be specified with ALTER SESSION SET TIME_ZONE = 'time_zone_region'.
• Time Zone Names: Oracle recognizes various time zone formats, including named regions (e.g., America/Los_Angeles) and offsets from UTC(e.g., UTC-08:00).
• Daylight Saving Time: Oracle handles DST automatically based on the chosen time zone and its corresponding rules.

In Oracle SQL, TIMESTAMP WITH TIME ZONE stores date, time, and associated time zone. For example:

Just as when defining a TIMESTAMP, you could also add a number in parentheses to specify fractional decimals. For example: 

When a column in a table is defined as TIMESTAMP WITH TIMEZONE, you should also specify the time difference. For example:

In the figure shown above, the TIMESTAMP value '2024-01-16 18:40:55' is based on the default time zone, which is the geolocation in where the database is located. On the other hand, when you specified the specific time zones, either based on UTC offset or the time zone names, the records will represent the associated time zone information:

Alternatively, you can also define a column as TIMESTAMP(n) WITH LOCAL TIME ZONE. This variation differs from TIMESTAMP WITH TIME ZONE in that the time zone offset is not stored with the column's value. Instead, when the value is retrieved, it is presented to the user in their local time zone: while the offset itself is not retained within TIMESTAMP WITH LOCAL TIME ZONE, the system dynamically computes the offset when the value is accessed from a different time zone. Thus, end users perceive the time in their respective local time zone, irrespective of the time zone where database server is located.  The value of n is representing the fractional decimals; if omitted, it defaults to 6.

Time Zone Variables, Operators, and Functions

Oracle also provides some built-in variables, operators, and functions to handle time zone values. The DBTIMEZONE is a variable that stores the database time zone, whereas SESSIONTIMEZONE stores the time zone of the current session. LOCALTIMESTAMP is another variable that stores the current timestamp in the session time zone. 

Note: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE are data types, not variables. Don't get confused with LOCALTIMESTAMP, which is a variable.

One of the operators that I find useful in practice is AT TIME ZONE. It converts a TIMESTAMP value on its left-hand side to the time zone on its right-hand side. For example:

Oracle built-in function FROM_TZ converts a timestamp literal to a TIMESTAMP WITH TIME ZONE value. For example: