Extracting time zone from Oracle JDBC TIMEZONETZ object

Question

Is there a way to extract timezone information directly from an oracle.sql.TIMESTAMPTZ object (selected from a TIMESTAMP WITH TIME ZONE column)?

Ideally, I'd like to be able to pull the time zone information directly out of the object without jumping through potentially expensive or fragile hoops (like converting things into strings and parsing those).

You would think that there is an easy way to do this, but I have yet to find one. Oracle's documentation is not very helpful. It claims the last two bytes returned by TIMESTAMPTZ#toBytes() encode the timezone information, but there is no description of our to actually decode this information.

Anyone out there have any experience dealing with this stuff?

Answer 1

Oracle has specific date format elements for these: TZD, TZH, TZM and TZR.

An example:

SQL> create table t (col timestamp with time zone)
  2  /

Tabel is aangemaakt.

SQL> insert into t values (sysdate)
  2  /

1 rij is aangemaakt.

SQL> select col
  2       , to_char(col,'TZD') time_zone_daylight_info
  3       , to_char(col,'TZH') time_zone_hour
  4       , to_char(col,'TZM') time_zone_minute
  5       , to_char(col,'TZR') time_zone_region
  6    from t
  7  /

COL                             TIME_Z TIM TI TIME_ZONE_REGION
------------------------------- ------ --- -- --------------------------------
22-05-09 09:12:33,000000 +02:00        +02 00 +02:00    

1 rij is geselecteerd.

Or using the EXTRACT function:

SQL> select col
  2       , extract(timezone_abbr from col)   time_zone_abbr
  3       , extract(timezone_hour from col)   time_zone_hour
  4       , extract(timezone_minute from col) time_zone_minute
  5       , extract(timezone_region from col) time_zone_region
  6    from t
  7  /

COL                             TIME_ZONE_                         TIME_ZONE_HOUR                       TIME_ZONE_MINUTE
------------------------------- ---------- -------------------------------------- --------------------------------------
TIME_ZONE_REGION
----------------------------------------------------------------
22-05-09 09:15:24,000000 +02:00 UNK                                             2                                      0
UNKNOWN


1 rij is geselecteerd.

Regards, Rob.