Timestamps & time zones - Frequently Asked Questions
(Doc ID 340512.1)
Last updated on JUNE 13, 2023
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 Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Information in this document applies to any platform.
Purpose
This note covers the FAQ for TIMESTAMP's and Time Zones in the Oracle database.
If you have a "wrong database time" (sysdate or systimestamp) please see first of all <note 1627439.1> How to diagnose wrong time ( SYSDATE and SYSTIMESTAMP) after DST change , server reboot , database restart or installation when connecting to a database on an Unix server .
DATEs and Calendars are covered separately in: <Note 227334.1> Dates & Calendars - Frequently Asked Questions, be aware that the influence of the OS time zone on functions like SYSDATE and SYSTIMESTAMP will be covered in that note, and not in this note.
For information about updates to the Oracle Database Timezone information please see <Note 412160.1> Updated DST transitions and new Time Zones in Oracle Time Zone File patches.
DATEs and Calendars are covered separately in: <Note 227334.1> Dates & Calendars - Frequently Asked Questions, be aware that the influence of the OS time zone on functions like SYSDATE and SYSTIMESTAMP will be covered in that note, and not in this note.
For information about updates to the Oracle Database Timezone information please see <Note 412160.1> Updated DST transitions and new Time Zones in Oracle Time Zone File patches.
Questions and Answers
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 |
| Questions and Answers |
| 1)What datatypes are using timezones? |
| 2)What is the difference between a timezone offset and a named timezone? |
| 3)What is the database time zone used for? And what does it not do? |
| 4)How can I check the database time zone? |
| 5)Why does the database time zone not change at the start or end of DST? |
| 6)Why is the database time zone the value it is, and how can I change it? |
| 7) Why do I get ORA-02231 (9i) or ORA-30079 ( 10g and up) when I try to change the database time zone? |
| 8) What is SYSTIMESTAMP ? |
| 9) What is the difference between CURRENT_DATE, LOCALTIMESTAMP and CURRENT_TIMESTAMP? |
| 10)Which time zones are available in Oracle? |
| 11)How can I change between the large and the basic time zone files? |
| 12)Which time zone file is currently used? |
| 13)How can I check the session time zone? |
| 14)How can I set the session time zone? |
| 15)What does "ORA-1804: failure to initialize timezone information" mean? |
| 16) Can I rely on Oracle time zones definitions? |
| 17)So what do I do if the Oracle time zone information is not up to date? |
| 18)How can I retrieve the time zone offset corresponding to a time zone region? |
| 20)How can I retrieve the time zone region (and abbreviation) for a given offset? |
| 21) Time zone names vs. Time zone abbreviations |
| 22)How can I compute the difference between two timestamp values? |
| 23) How can I see the abbreviation for my time zone? |
| 24)What are reasons to get ORA-1882? |
| 25)How does Oracle Forms work with time zones? |
| 26)I have some troubles with DBMS_SCHEDULER and timezones. |
| 27)Where can I find more external information about time zones? |
| 28) Why does using Datetime Arithmetic on datatypes having timezone information seams to give incorrect result ? |
| 29) When comparing TSLTZ and TSTZ output the result around or after DST times seams wrong. |
| 30) extract TIMEZONE_MINUTE gives a negative result |
| 31) How to cast a DATE or TIMESTAMP to another timezone? |
| 32) Should I worry when moving data from a database in one timezone into a database in an other timezone using export/import or dblinks? |
| 33) How to get the time in UTC , GMT or epoch (Unix) time in Oracle? |
| 34) Is there a way to get the "server" (= Operating system) timezone setting in SQL? |
| 99) Known issues related to timezone usage in sql |
| References |