TimeZone

Working with Timezones in Oracle

Posted on Updated on

Calculate the Timezone Offset

SELECT TZ_OFFSET('US/Pacific') FROM DUAL;

The timezone offset can change based on the current time, as this takes into account if the current date is Daylight Saving (DT) or Standard (ST).

So result of TZ_OFFSET is never constant, it depends on the current date as well!

Working with the TIMESTAMP WITH TIMEZONE datatype

You can represent the same time in different timezones. The TIMESTAMP WITH TIMEZONE datatype is tagged already for a particular timezone, you can convert it to move it into a different timezone. Allthough the time is in essence the same, it will look very different when output.

Moving a TIMESTAMP WITH TIMEZONE to a different Time Zone can be simply done using the following syntax:

datetimetz AT TIME ZONE tz

Where datetimetz is any value of type TIMESTAMP WITH TIMEZONE, and tz is a string indicating the TZ you want to convert over.

Example:

SELECT tz, TO_CHAR(ts AT TIME ZONE tz,format) AS ts
FROM
(SELECT SYSTIMESTAMP AS ts FROM dual),
(SELECT 'UTC' AS tz FROM dual),
(SELECT 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM (TZD, TZR)' AS format FROM dual);

Note we are using TO_CHAR, to control the output of the time, by explicitly converting it to a VARCHAR.

Time Zones

Time Zones can be expressed in two ways:

  • As on Offset: '-06:00', which also matches the 'TZH:TZM' TO_CHAR format.
  • or, as Region Name: America/Denver‘

It is important to note that if you need to automatically adjust for Daylight Saving, to make sure to use the Region Name rather than just an offset. An offset is always fixed at the offset provided, and will never adjust for Daylight Saving time.

More information can be found at:

Note that even for the Region Name, in Oracle, there are multiple options for the same region. However sometimes the names and abreviations are conflicting. It is highly recomended to use the official long form as in 'America/Los_Angeles'. If you would use the short form 'PST', it is first of all dubious whether this means only Standard Time, or if it should adjust for Daylight Saving Time. Also the same abreviations might be custom in two totally different areas.

Oracle considders PDT, PST, and America/Los_Angeles to be all synonyms. This means they will all adjust for Daylight Saving Time.

To see a list of official Timezone Names, and their abbreviated alias in Oracle, you can use following Query:

SELECT *
FROM gv$timezone_names

Or you can extend this query to lear the exact Offset

SELECT<
tzname,
tzabbrev,
tz_offset_now,
tz_offset_later,
CASE WHEN tz_offset_now != tz_offset_later THEN 'Y' ELSE 'N' END AS supports_dst
FROM (
SELECT
tzname,
tzabbrev,
TO_CHAR((SYSTIMESTAMP AT TIME ZONE tzname)                     ,'TZH:TZM (TZD)') AS tz_offset_now,
TO_CHAR((SYSTIMESTAMP AT TIME ZONE tzname) + INTERVAL '6' MONTH,'TZH:TZM (TZD)') AS tz_offset_later
FROM gv$timezone_names
)

We did not use the TZ_OFFSET function to figure out the Timezone Offset, because it is not parameterized, and as a consequence it only works on the current time (now).

Used SQL Functions: SYSTIMESTAMP, TO_CHAR

One More example:

SELECT
  tz,
  TO_CHAR(ts AT TIME ZONE tz                     ,format) AS ts_now,
  TO_CHAR((ts AT TIME ZONE tz) + INTERVAL '6' MONTH,format) AS ts_later
FROM
  (SELECT SYSTIMESTAMP AS ts FROM dual),
  (
              SELECT 'UTC'              AS tz FROM dual
    UNION ALL SELECT 'GMT'              AS tz FROM dual
    UNION ALL SELECT 'MST'              AS tz FROM dual
    UNION ALL SELECT 'PST'              AS tz FROM dual
    UNION ALL SELECT 'US/Mountain'      AS tz FROM dual
    UNION ALL SELECT 'America/Denver'   AS tz FROM dual
    UNION ALL SELECT 'America/Phoenix'  AS tz FROM dual
  ),
  (
     SELECT
       'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM (TZD, TZR)' AS format
     FROM dual
  );

SYSTIMESTAMP is a TIMESTAMP WITH TIMEZONE, while SYSDATE is just of type DATE which does not carry Timezone information. SYSDATE is always in the timezone of the Database.

Date Calculations

If the datatype is of type DATE, you can operate date calculations on it by simply adding +1 for one day, or +1/24 for one hour (and other fractions). If you are working with a TIMESTAMP WITH TIMEZONE data type however, the correct way is to use an INTERVAL.

The interval was already used in previous examples too:

SELECT
TO_CHAR((SYSTIMESTAMP AT TIME ZONE 'UTC')+INTERVAL '6' MONTH,'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM (TZD, TZR)')
FROM dual

Conversion

It was already shown how you can convert either DATE or TIMESTAMP WITH TIMEZONE to a VARCHAR by means of the TO_CHAR function.

You can also convert a SYSDATE into a TIMESTAMP WITH TIMEZONE:

First Cast it to a TIMESTAMP, and then apply the appropriate TZ using the oracle FROM_TZ function to get a TIMESTAMP WITH TIMEZONE. After having it in a TIMESTAMP WITH TIMEZONE data type, you move it to different Timezones using the AT TIME ZONE syntax.

A TIMESTAMP (with or without TZ), you can convert back to simple type of DATE using a Cast.

SELECT
  CAST(
    FROM_TZ(
      CAST( (TRUNC(SYSDATE)-1) AS TIMESTAMP),
      'US/Pacific'
    ) AT TIME ZONE 'UTC'
    AS DATE
  )
FROM DUAL

Used SQL Functions SYSDATE, TRUNC, FROM_TZ

Functions returning current time or date

The details are in the fine print of the documentation. Take a look at the Return Type, and the actual Timezone the DATE or TIMESTAMP is calculated in.

  1. SYSDATE
    • Return Type: DATE
    • Time Zone: Host OS of Database Server
  2. CURRENT_DATE
    • Return Type: DATE
    • Time Zone: Session
  3. SYSTIMESTAMP
    • Return Type: TIMESTAMP WITH TIME ZONE
    • Time Zone: Host OS of Database Server
  4. CURRENT_TIMESTAMP
    • Return Type: TIMESTAMP WITH TIME ZONE
    • Time Zone: Session
  5. LOCALTIMESTAMP
    • Return Type: TIMESTAMP
    • Time Zone: Session
  6. DBTIMEZONE
    • Time Zone: DB Time Zone. Inherits from DB Server OS, but can be overridden using set at DB Creation or Alter using Database Parameter SET TIME_ZONE=.... This affects the time zone used for TIMESTAMP WITH LOCAL TIME ZONE datatypes.
  7. SESSIONTIMEZONE
    • Time Zone: Session Time zone. Inherits from Session hosting OS, but can be overridden using Session Parameter ALTER SESSION SET TIME_ZONE=....

Return Type, indicates whether or not the Timezone is available within the Datatype. If you try to print TZR if datatype does not carry TimeZone, then it will just show up as +00:00 (doesn’t mean it is GMT). Otherwise It will show the TimeZone matching either the Database or Session as indicated.

Time Zone, indicates in which Timezone the time is calculated. For matching TimeZone, the same Date/Time will be shown (HH24:MI).

Note that none of the Functions return the time in the Time Zone set with the Database Time Zone (or as returned by the DBTIMEZONE function). That is, none of the functions also return a datatype of TIMESTAMP WITH LOCAL TIME ZONE. However you can convert the output of any of the functions that does return a timezone into a different timezone (including DBTIMEZONE) as follows:

SELECT SYSTIMESTAMP AT TIME ZONE DBTIMEZONE FROM DUAL;

Related articles

This entry was posted in sql and tagged , , , , .