Here's my time zone quick reference card.
Datatypes - Timestamp/Timezone Functions - Date functions - Variables/parameters - Timezone files - Environment variables - Date format mask elements - Examples - Database time zone - Time zones and the Scheduler - Documentation links
| Datatypes | ||
|---|---|---|
| Timestamp/timezone datatype | What Oracle stores | What Oracle displays |
| TIMESTAMP WITH TIME ZONE | Year, month, day, hour, minute, second, fractional second, and time zone displacement (HH:MI difference from GMT) | Stored value |
| TIMESTAMP WITH LOCAL TIME ZONE | Year, month, day, hour, minute, second, fractional second; does NOT store time zone information, but instead converts data to the database time zone and stores it w/o time zone information | Converts the stored data to the session's time zone before displaying |
| Timestamp/timezone functions | |||
|---|---|---|---|
| Timestamp/timezone function | What it returns | Return type | |
| SYSTIMESTAMP | Current date/time, in Database TZ | TIMESTAMP WITH TIME ZONE | |
| CURRENT_TIMESTAMP | Current date/time, in Client Session TZ | TIMESTAMP WITH TIME ZONE | |
| LOCALTIMESTAMP | Local date/time in Client Session, but with no TZ info | TIMESTAMP | |
| DBTIMEZONE | Database time zone, in HH:MI offset from GMT | VARCHAR2 | |
| SESSIONTIMEZONE | Session time zone, in HH:MI offset from GMT | VARCHAR2 | |
| EXTRACT (part FROM date_time) | Extracts year, hour, seconds, time zone name, etc. from a supplied datetime or interval expression. | VARCHAR2 | |
| SYS_EXTRACT_UTC(date_time with TZ) | GMT (UTC) time of date/time supplied | TIMESTAMP | |
| TZ_OFFSET(TZ) | Returns hour/minute offset from GMT of TZ | VARCHAR2 | |
| FROM_TZ(timestamp,TZ) | Converts a TIMESTAMP to TIMESTAMP WITH TIME ZONE | TIMESTAMP WITH TIME ZONE | |
| TO_TIMESTAMP | Convert char + fmt model to TIMESTAMP | TIMESTAMP | |
| TO_TIMESTAMP_TZ | Convert char + fmt model to TIMESTAMP WITH TIME ZONE | TIMESTAMP WITH TIME ZONE | |
| TO_DSINTERVAL | Convert char to INTERVAL DAY TO SECOND
Format is 'DAYS HH24:MI:SS', eg. '0 1:13' | INTERVAL DAY TO SECOND | |
| TO_YMINTERVAL | Convert char to INTERVAL YEAR TO MONTH
Format is 'YY-MM', eg. '01-02' | INTERVAL YEAR TO MONTH | |
| Date functions | ||
|---|---|---|
| Date function | What it returns | Return type |
| SYSDATE | Current date/time, as provided by the DB Server's O/S | Date |
| CURRENT_DATE | Current date/time, in the Session TZ | NUMBER |
| Timestamp/timezone variables/parameters | ||||
|---|---|---|---|---|
| Timestamp/timezone variable/parameter | Scope | What it sets | Allowed values | Example |
| TIME_ZONE | Session, Database | Time zone | Any valid Oracle TZ name, eg 'US/Eastern'. Query V$TIMEZONE_NAMES for a full listing. | alter session set TIME_ZONE = '+02:00'; |
| NLS_TIMESTAMP-_TZ_FORMAT | Session, system | The default timestamp-with-timezone format to use with TO_CHAR and TO_TIMESTAMP_TZ functions | Any supported date format mask | alter session set NLS_TIMESTAMP_TZ_FORMAT = 'hh24:mi tzh:tzm'; |
| ERROR_ON_OVERLAP-_TIME | Session | Whether Oracle interprets ambiguous time expr. at the end of DST as Standard Time, or returns an error | True|False | ALTER SESSION SET ERROR_ON_OVERLAP_TIME = true |
| Environment variables | |||
|---|---|---|---|
| Variable | What it sets | Set on client or server? | Example |
| ORA_TZFILE | Time zone file used by the database | Server | ORA_TZFILE
= '/u01/opt/oracle/oracore/zoneinfo/timezone.dat' |
| ORA_SDTZ | Default session time zone | Client | ORA_SDTZ = 'DB_TZ' |
'OS_TZ' | '[+|-]HH:MI' | 'timezone_name' |
| Useful date format elements for timestamps/timezones | ||
|---|---|---|
| Element | What it is | Example |
| HH24 | Hours, on 24-hour clock | 'HH24:MI:SSXFF' -> '14:03:23.9876' |
| MI | Minutes | 'HH24:MI:SSXFF' -> '14:03:23.9876' |
| SS | Seconds | 'HH24:MI:SSXFF' -> '14:03:23.9876' |
| X | Fractional separator for seconds | 'HH24:MI:SSXFF' -> '14:03:23.9876' |
| FF | Fractional amount of seconds | 'HH24:MI:SSXFF' -> '14:03:23.9876' |
| TZH | Hours in time-zone displacement | 'TZH:TZM' -> '-05:00' |
| TZM | Minutes in time-zone displacement | 'TZH:TZM' -> '-05:00' |
| Timestamp/timezone files | |||
|---|---|---|---|
| Timezone file | What it contains | Default? | How to switch to it |
| $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat | All the time zone names | Default in 10g; non-default in 9i | 10g: no action necessary; 9i: shut down server, set ORA_TZFILE env variable file name, start up again |
| $ORACLE_HOME/oracore/zoneinfo/timezone.dat | Only the most commonly used time zone names | Default in 9i; non-default in 10g | 9i: no action necessary; 10g: set ORA_TZFILE as above ONLY if you're sure that no data in the database uses a time zone that's only in the larger file |
SQL> SQL> aLTER SESSION SET time_zone = local; Session altered. SQL> select sessiontimezone from dual; SESSIONTIMEZONE --------------------------------------------------------------------------- -05:00 SQL> --set local time zone to GMT + 5 hrs 44 mins SQL> ALTER SESSION SET time_zone = '+05:44'; Session altered. SQL> SELECT to_char(sysTIMESTAMP,'HH24:MI:SS.FF') 2 ,to_char(LOCALTIMESTAMP,'HH24:MI:SS.FF') 3 ,to_char(current_TIMESTAMP,'HH24:MI:SS.FF') 4 FROM dual; TO_CHAR(SYSTIMESTA TO_CHAR(LOCALTIMES TO_CHAR(CURRENT_TI ------------------ ------------------ ------------------ 10:07:11.631904 20:51:11.631920 20:51:11.631920 SQL> --TZH:TZM part of date format mask: hour/minute offset from GMT SQL> --note: no timezone info with LOCALTIMESTAMP SQL> --so TZH:TZM cannot be used in date fmt mask SQL> SELECT to_char(sysTIMESTAMP,'HH24:MI:SS.FF TZH:TZM') 2 --,to_char(LOCALTIMESTAMP,'HH24:MI:SS.FF') 3 ,to_char(current_TIMESTAMP,'HH24:MI:SS.FF TZH:TZM') 4 FROM dual; TO_CHAR(SYSTIMESTAMP,'HH2 TO_CHAR(CURRENT_TIMESTAMP ------------------------- ------------------------- 10:07:11.739155 -05:00 20:51:11.739171 +05:44 SQL> aLTER SESSION SET time_zone = local; Session altered. SQL> select sessiontimezone from dual; SESSIONTIMEZONE --------------------------------------------------------------------------- -05:00 SQL> SQL> --SYS_EXTRACT_UTC() SQL> SELECT to_char(sysTIMESTAMP,'HH24:MI:SS.FF'), 2 to_char(sys_extract_utc(systimestamp),'HH24:MI:SS.FF') 3 from dual; TO_CHAR(SYSTIMESTA TO_CHAR(SYS_EXTRAC ------------------ ------------------ 10:02:05.192715 15:02:05.192715 SQL>
"The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns. Oracle recommends that you set the database time zone to UTC (0:00) to avoid data conversion and improve performance when data is transferred among databases. This is especially important for distributed databases, replication, and exporting and importing." (10gR2 Globalization Support Guide, Chapter 4)
"If the database time zone or the session time zone has not been set manually, Oracle uses the operating system time zone by default. If the operating system time zone is not a valid Oracle time zone, then Oracle uses UTC as the default time zone." (10gR2 Concepts)
"Oracle Database normalizes all new TIMESTAMP WITH LOCAL TIME ZONE data to the time zone of the database when the data is stored on disk. Oracle Database does not automatically update existing data in the database to the new time zone. Therefore, you cannot reset the database time zone if there is any TIMESTAMP WITH LOCAL TIME ZONE data in the database. You must first delete or export the TIMESTAMP WITH LOCAL TIME ZONE data and then reset the database time zone. For this reason, Oracle does not encourage you to change the time zone of a database that contains data." (10gR2 SQL Reference: ALTER DATABASE)
SQL> select DBTIMEZONE from dual; DBTIME ------ +00:00
"The calendaring syntax does not allow you to specify a time zone. Instead the scheduler retrieves the time zone from the start_date argument. If jobs must follow daylight savings adjustments you must make sure that you specify a region name for the time zone of the start_date. For example specifying the start_date time zone as 'US/Eastern' in New York will make sure that daylight saving adjustments are automatically applied. If instead the time zone of the start_date is set to an absolute offset, such as '-5:00', daylight savings adjustments are not followed and your job execution will be off by an hour half of the year." (10gR2 PL/SQL Supplied Packages ch. 83)