Oracle Timestamp With Time Zone

Last week Warren left a comment on my story Converting Time Zones in Oracle asking how he could output dates with time zone like: “17-Mar-2006 14:30:00 EST”.

Well, after a bit of digging it turns out the answer is not as simple as it sounds. The traditional Oracle DATE and TIMESTAMP datatypes don’t store time zone information. A workaround might be to store time zone information in a separate column in the table, but that seems like it could cause some confusion.

Well, it looks like in version 9i Oracle has added a new datatype to handle exactly this. The TIMESTAMP WITH TIME ZONE datatype allows a time zone to be stored with a date and time either in offset from UTC or by abbreviation.

The TIMESTAMP WITH TIME ZONE datatype can be declared in the table definition anywhere you would have used DATE or TIMESTAMP. To store a date/time/time zone into a row Oracle has also added the function TO_TIMESTAMP_TZ which acts much like the familiar TO_DATE function, however will recognize TZH, TZM, TZR, and TZD for time zone hour, minute, region, and abbreviation respectively.

To retrieve time zone information you can apply the same new abbreviations to the familiar to_char function when selecting a column of type TIMESTAMP WITH TIME ZONE.

Rather than go into any more detail here, check out this article from Oracle Magazine. It covers the topic very well including example code.

sql, oracle, database administration, database, dba, database development, dbms, pl/sql

6 thoughts on “Oracle Timestamp With Time Zone”

  1. I hope to know the reason why a single timezone has more than one abbrebiations in the V$TIMEZONE_NAMES .

  2. Vikram,

    Some of the abbreviations are for daylight savings time, e.g. US/Eastern EST – Eastern Standard Time, US/Eastern EDT – Eastern Daylight Savings Time.

    Other abbreviations like EWT (Eastern War Time) are largely for historical support. (EWT was used in the US durring World War II). Of course there were no Oracle databases durring WWII, but that doesn’t mean we won’t want to represent dates from that time period.

    Hope this helps.

  3. What to be the OS Level DATE and TIME when the ORACLE Database Server and Application Server setup in the US and accessed from Australia?.
    What Changes to be done in the App and Database server for Date and Time Format. What typ of data type to be used in the Column level for date and time?

    Iam thinking the column level can be given the date datatype but the parameters for the $TIMEZONE and the OS LEVEL US or Aus DATE and TIME to be used?
    What should be the DBTIMEZONE? in the Database Server.
    Pls let me know the question you understand.?

Leave a Reply

Your email address will not be published. Required fields are marked *