Java – How to get the time zone ID instead of the time zone offset from Oracle

Is there a way to get the timezone ID from oracle instead of the time zone offset?
WHEN I EXECUTE SELECT SYSTIMESTAMP AS TIMEZONE FROM DUAL; , the result returned is 13-JAN-21 10.19.52.936031000 AM +05:30.

When I retrieve it from Java using rs.getObject("TIMEZONE", ZonedDateTime.class ), the ZonedDateTime object has both offset and zoneID set to +05:30.

BUT WHAT I WOULD LIKE TO SEE IS, SELECT SYSTIMESTAMP AS TIMEZONE FROM DUAL; 04-JAN-21 02.40.50.000000000 PM ASIA/COLOMBO and Java ZonedDateTime should be returned For queries, offset should be set to +05:30 and zoneID should be set to Asia/ Colombo。

Is there a way to set this at the database level or at the Java level? THE CURRENT DBTIMEZONE SETTING IS +05:30.

If the data is already in an Oracle SQL table and you have to convert to a timestamp with a time zone (for example, in a new column that you created in the same table), you don’t need to explicitly go to the OS, or use Java or anything else, not the Oracle database itself.

If you have to assume that “date” refers to the server time zone (the

“database” you refer to usually 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 parameter, if that’s what you need.

This assumes that the database (and/or session) time zones are set correctly in the database in the client, respectively. If not/they are not, they need to be fixed first. If the parameters are set correctly first, Oracle is well positioned to handle daytime savings. (If not, it’s not clear why you’d try to make your operation “more correct” than the database supports in the first place.) )

Example: In the WITH clause below, I simulate a table where the data type of column dt is date. Then I converted it to a timestamp with a time zone, in my session (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

As a reference, you can check this link StackOverflow Ref. Link