Time Zone Quick Reference

Home

Toolkit:

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 datatypeWhat Oracle storesWhat Oracle displays
TIMESTAMP WITH TIME ZONEYear, month, day, hour, minute, second, fractional second, and time zone displacement (HH:MI difference from GMT)Stored value
TIMESTAMP WITH LOCAL TIME ZONEYear, 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 informationConverts the stored data to the session's time zone before displaying

Timestamp/timezone functions
Timestamp/timezone functionWhat it returnsReturn type
SYSTIMESTAMPCurrent date/time, in Database TZTIMESTAMP WITH TIME ZONE
CURRENT_TIMESTAMPCurrent date/time, in Client Session TZTIMESTAMP WITH TIME ZONE
LOCALTIMESTAMPLocal date/time in Client Session, but with no TZ infoTIMESTAMP
DBTIMEZONEDatabase time zone, in HH:MI offset from GMTVARCHAR2
SESSIONTIMEZONESession time zone, in HH:MI offset from GMTVARCHAR2
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 suppliedTIMESTAMP
TZ_OFFSET(TZ)Returns hour/minute offset from GMT of TZVARCHAR2
FROM_TZ(timestamp,TZ)Converts a TIMESTAMP to TIMESTAMP WITH TIME ZONE TIMESTAMP WITH TIME ZONE
TO_TIMESTAMPConvert char + fmt model to TIMESTAMPTIMESTAMP
TO_TIMESTAMP_TZConvert char + fmt model to TIMESTAMP WITH TIME ZONE TIMESTAMP WITH TIME ZONE
TO_DSINTERVALConvert char to INTERVAL DAY TO SECOND
Format is 'DAYS HH24:MI:SS', eg. '0 1:13'
INTERVAL DAY TO SECOND
TO_YMINTERVALConvert char to INTERVAL YEAR TO MONTH
Format is 'YY-MM', eg. '01-02'
INTERVAL YEAR TO MONTH

Date functions
Date functionWhat it returnsReturn type
SYSDATECurrent date/time, as provided by the DB Server's O/SDate
CURRENT_DATECurrent date/time, in the Session TZNUMBER

Timestamp/timezone variables/parameters
Timestamp/timezone variable/parameterScopeWhat it setsAllowed valuesExample
TIME_ZONESession, DatabaseTime zoneAny 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_FORMATSession, systemThe default timestamp-with-timezone format to use with TO_CHAR and TO_TIMESTAMP_TZ functionsAny supported date format maskalter session set NLS_TIMESTAMP_TZ_FORMAT = 'hh24:mi tzh:tzm';
ERROR_ON_OVERLAP-_TIMESessionWhether Oracle interprets ambiguous time expr. at the end of DST as Standard Time, or returns an errorTrue|FalseALTER SESSION SET ERROR_ON_OVERLAP_TIME = true

Environment variables
VariableWhat it setsSet on client
or server?
Example
ORA_TZFILETime zone file used by the databaseServerORA_TZFILE =
'/u01/opt/oracle/oracore/zoneinfo/timezone.dat'
ORA_SDTZDefault session time zoneClientORA_SDTZ = 'DB_TZ' | 'OS_TZ'
| '[+|-]HH:MI' | 'timezone_name'

Useful date format elements for timestamps/timezones
ElementWhat it isExample
HH24Hours, on 24-hour clock'HH24:MI:SSXFF' -> '14:03:23.9876'
MIMinutes'HH24:MI:SSXFF' -> '14:03:23.9876'
SSSeconds'HH24:MI:SSXFF' -> '14:03:23.9876'
XFractional separator for seconds'HH24:MI:SSXFF' -> '14:03:23.9876'
FFFractional amount of seconds'HH24:MI:SSXFF' -> '14:03:23.9876'
TZHHours in time-zone displacement'TZH:TZM' -> '-05:00'
TZMMinutes in time-zone displacement'TZH:TZM' -> '-05:00'

Timestamp/timezone files
Timezone fileWhat it containsDefault?How to switch to it
$ORACLE_HOME/oracore/zoneinfo/timezlrg.datAll the time zone namesDefault in 10g; non-default in 9i10g: no action necessary; 9i: shut down server, set ORA_TZFILE env variable file name, start up again
$ORACLE_HOME/oracore/zoneinfo/timezone.datOnly the most commonly used time zone namesDefault in 9i; non-default in 10g9i: 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

Examples

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>

Database time zone

"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

Time zones and the Scheduler

"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)

Documentation links (10gR2)

Related articles


Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...