java - How to gather timezone of operating system from Oracle database in string format? (Migrate/convert date to ts with tz)

Question

I want to update a database table which uses date type to timestamp with timezone type in such a way that the old dates get correct timezone information.

The plain cast is not good for me because if the time zone is for example UTC+2 hours (UTC+1 + 1 hour for daylightsaving) and I try to cast dates to timestamp with timezone, all the dates in the database table got the same +2 hour as timezone offset, regardless if it's a summer time or winter time date.

I already can write an SQL query which can determine about a date if it is in daylightsaving time or not, IF I know the current time zone in string format, e.g. 'Europe/Berlin'. The problem is that dbtimezone and sessiontimezone can be stored in other formats, too (+02:00, CET, etc). I cannot easily set the current sessiontimezone in a static way, because there are customers in several places on the globe with their own databases, but using a common update script. Express method for timestamp can not help neither, because it cannot map the offset to named time zones.

I've seen a solution which uses java stored procedure to get the OS’s timezone instead of Oracles timezone. Unfortunately we use Oracle 12c, which contains an older JRE (I think it's 1.6 version). So, although Java 1.8 handles the timezones and daylight saving well (it uses updated tzmapping table), it does not work for me. I tryed it and if I run a test from Netbeans, then it gives me back the right time zone ID (in Europe/Berlin format), but even if it is accepted by Oracle SQL Developer, SQLPlus (which we use for running update scrips), it displays only +02:00.

I've tryied to use JodaTime (recompiled onto Java 1.6 in order to be accepted by SQL*Plus). The latest JodaTimes uses its own mapping table in theory. I read here on StackOverflow that if it cannot gather the time zone from user.timezone variable, then it turns to java.util, which is not good, as I mentioned. And if it does not succeed, then uses UTC. But it's not clear to me why it cannot get timezone from user.timezone systemm variable. Is it a permission problem maybe? Or how could I possibly solve this issue? Thank you!

Answer 1

If the data is already in an Oracle SQL table, and you must convert to a timestamp with time zone (for example, in a new column you created in the same table), you do not need to go explicitly to the OS, or to use Java or any other thing, other than the Oracle database itself.

It is not clear from your question if you must assume the "date" was meant to be in the server time zone (you mention "the database" which normally means the server) or the client time zone (you mention "session" which means the client). Either way:

update <your_table>
set <timestamp_with_time_zone_col> = 
            from_tz(cast<date_col> as timestamp, dbtimezone)
;

or use sessiontimezone as the second argument, if that's what you need.

This assumes that the database (and/or the session) time zone is set up properly in the db, respectively in the client. If it isn't / they aren't, that needs to be fixed first. Oracle is perfectly capable of handling daytime savings time, if the parameters are set correctly in the first place. (And if they aren't, it's not clear why you would try to get your operation to be "more correct" than the database supports in the first place.)

Example: in the WITH clause below, I simulate a table with a column dt in data type date. Then I convert that to be a timestamp with time zone, in my session's (client) time zone.

with
  my_table ( dt ) as ( 
    select to_date('2018-06-20 14:30:00', 'yyyy-mm-dd hh24:mi:ss') from dual 
  )
select dt,
       from_tz(cast(dt as timestamp), sessiontimezone) as ts_with_tz
from   my_table
;

DT                  TS_WITH_TZ                                       
------------------- -------------------------------------------------
2018-06-20 14:30:00 2018-06-20 14:30:00.000000000 AMERICA/LOS_ANGELES