How to Determine Whether Time Zone Changes Will Affect Your Database
(Doc ID 406410.1)
Last updated on FEBRUARY 01, 2022
Applies to:
Oracle Database - Enterprise Edition - Version 9.0.1.0 and laterOracle Database - Standard Edition - Version 9.0.1.0 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Information in this document applies to any platform.
Purpose
This document explains how you can determine whether time zone changes will affect your database.
It discusses all possible uses of time zone information from the Oracle Time Zone files in the Oracle database, and how to deal with this during a update of these time zone files.
An update of database time zone files can affect already stored data that uses these time zone zone files in the database. Also functions that use the time zone files can be affected by the installation of new time zone files.
* The Oracle kernel itself is also not impacted by the OS/system clock changes (or for that matter Oracle DST patches). <Note 1013279.6> How Does Daylight Savings Time Affect The Database Kernel?
* the "Date" datatype has no timezone information stored
* "sysdate" (and "systimestamp") do not use any Oracle provided timezone information. Sysdate is pure depending on the Operating system clock. It may however be depending on the timezone information of this operating system and/or the Operating system TZ variable settings when the database and listener where started (!!!). The <Note 227334.1> - Dates & Calendars - Frequently Asked Questions has a complete overview of the sysdate behavior. Please note that this, while it may affect Oracle, is NOT depending on an Oracle setting or patch but a pure OS setting.
* We strongly recommend to also have a look at these 2 FAQ's when you are not familiar with Timezone usage.
<Note 340512.1> - Timestamps & time zones - Frequently Asked Questions
<Note 227334.1> - Dates & Calendars - Frequently Asked Questions
Scope
The Oracle time zone files are used for TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) and TIMESTAMP WITH TIME ZONE (TSTZ) datatypes and the TZ_OFFSET function. These datatypes can be used as column types or as PL/SQL types, for example as result types of functions, or as parameters or variable types.
The time zone files files are part of the database installation, changes to DST rules mean that new time zone files need to be installed. This might affect data that is already stored in the database using previous rules. This note will discuss all the possible uses of the information from the time zone files, and how to deal with this during a update of the time zone files.
Where this note discusses how these different types of time zone usage can affect a database, the following note provides practical views that will help the DBA to assess if any of these are indeed used in a database:
<Note 412971.1> SCRIPT: cre_tz_views.sql - Assess Time Zone usage in a Database
Details
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Purpose |
| Scope |
| Details |
| Stored TIMESTAMP WITH TIME ZONE data |
| Stored TIMESTAMP WITH LOCAL TIME ZONE data |
| The Database Time Zone |
| TIMESTAMP WITH LOCAL TIME ZONE or TIMESTAMP WITH TIME ZONE as PL/SQL types |
| Creation of TSTZ data through TO_TIMESTAMP_TZ and FROM_TZ functions or as TIMESTAMP literal |
| Use of time zone data through the CURRENT_TIMESTAMP, CURRENT_DATE or LOCALTIMESTAMP functions |
| Use of time zone data through the AT TIME ZONE function |
| Use of time zone data through the EXTRACT or SYS_EXTRACT_UTC functions |
| Use of time zone data through the TZ_OFFSET function |
| Use of timezone for job scheduling |
| References |