How to get the timezone ID from Oracle instead timezone offset

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

For Reference You can Check this Link StackOverflow Ref. Link

In a sense, you are out of luck. SYSTIMESTAMP simply returns what your operating system reports. The time zone is the time zone of the computer system hosting your database. It is not the database time zone (which may be something completely unrelated to where the DB actually resides) or the session time zone or anything like that. You will need to make the change in the db host OS itself, if you can.

If you know for sure that the system that hosts your database is in Sri Lanka, then you can get what you want indirectly like so:

select systimestamp AT TIME ZONE 'ASIA/COLOMBO' from dual;

You can set the timezone at database level using ALTER database command as follows:

alter database set time_zone = '+05:30';

Please note that this will take effect after you restart your DB.

shutdown immediate
startup

The Oracle TZ_OFFSET() function returns the time zone offset from UTC of a valid time zone name or the SESSIONTIMEZONE or DBTIMEZONE function name.

TZ_OFFSET(value)

he TZ_OFFSET() function accepts one argument which can be a valid time zone name e.g., ‘Europe/London’, a function name of SESSIONTIMEZONE or DBTIMEZONE, or a time zone offset from UTC (which simply returns itself).

SELECT
  TZ_OFFSET( 'Europe/London' )
FROM
  DUAL;

The result would be +01:00

This is how you can get timezone.

SELECT systimestamp AS DBTime,
       systimestamp at time zone 'Europe/London',
       systimestamp at time zone 'America/Sao_Paulo'from dual;

SELECT
  TZ_OFFSET( DBTIMEZONE )
FROM
  DUAL;

SYSTIMESTAMP is returned in the database operating system's time zone - so there is no way to change it within the database.

Don't mix the database operating system's time zone with DBTIMEZONE, they are different.

Also note, time zone Asia/Colombo is different to time zone +05:30 although they have (currently) the same UTC Offset.

Time zone Asia/Colombo considers daylight saving times in case it would apply, and it also covers changes. For example Asia/Colombo changed in 2006 from UTC+06:00 to UTC+05:30. Thus TZ_OFFSET(TIMESTAMP '2020-01-01 12:00:00 Asia/Colombo') returns a different offset than TZ_OFFSET(TIMESTAMP '2000-01-01 12:00:00 Asia/Colombo'), for example.

Time zone +05:30 is always 5:30h before UTC.

Your question is not really clear, what do you like to get?