Retrieving Time Zone from Oracle JDBC TIMEZONETZ Object
Is there a way to extract timezone information directly from an object oracle.sql.TIMESTAMPTZ
(selected from a column TIMESTAMP WITH TIME ZONE
)?
Ideally, I would like to get the timezone information directly from the object, without jumping over potentially costly or fragile hoops (like converting things to strings and parsing them).
You would think there is an easy way to do this, but I haven't found one yet. Oracle documentation is not very helpful. It claims that the last two bytes returned TIMESTAMPTZ#toBytes()
encode timezone information, but there is no description of how we actually decode this information.
Anyone have any experience with this stuff?
a source to share
Oracle has special date format elements for these: TZD, TZH, TZM, and TZR.
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.
a source to share
The TIMESTAMPTZ format is well documented . We are currently using the following code to retrieve timezone information.
If you are using Java SE 8, you can use getObject(int, OffsetDateTime.class)
, if you are using getObject(int, ZonedDateTime.class)
, you may be affected by bug 25792016 .
a source to share