By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Select
EXTRACT(HOUR FROM (FROM_TZ(CAST(SYSTIMESTAMP AS TIMESTAMP), 'UTC') at time zone 'Europe/Helsinki')) as hour,
TO_CHAR(FROM_TZ(CAST(SYSTIMESTAMP AS TIMESTAMP), 'UTC') at time zone 'Europe/Helsinki','HH24') as hour2
from dual
| HOUR | HOUR2 |
|---|---|
| 11 | 14 |
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS'
alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS TZR'
select
SYSTIMESTAMP,
FROM_TZ(CAST(SYSTIMESTAMP AS TIMESTAMP), 'UTC') at time zone 'Europe/Helsinki' as wrong_time,
EXTRACT(HOUR FROM (FROM_TZ(CAST(SYSTIMESTAMP AS TIMESTAMP), 'UTC') at time zone 'Europe/Helsinki')) as hour,
TO_CHAR(FROM_TZ(CAST(SYSTIMESTAMP AS TIMESTAMP), 'UTC') at time zone 'Europe/Helsinki','HH24') as hour2
from dual
| SYSTIMESTAMP | WRONG_TIME | HOUR | HOUR2 |
|---|---|---|---|
| 2021-06-04 11:39:41 +01:00 | 2021-06-04 14:39:41 EUROPE/HELSINKI | 11 | 14 |
select
SYSTIMESTAMP,
SYSTIMESTAMP at time zone 'Europe/Helsinki' as right_time,
EXTRACT(HOUR FROM SYSTIMESTAMP at time zone 'Europe/Helsinki') as wrong_hour,
EXTRACT(HOUR FROM CAST(SYSTIMESTAMP at time zone 'Europe/Helsinki' AS TIMESTAMP)) as hour,
TO_CHAR(SYSTIMESTAMP at time zone 'Europe/Helsinki','HH24') as hour2
from dual
| SYSTIMESTAMP | RIGHT_TIME | WRONG_HOUR | HOUR | HOUR2 |
|---|---|---|---|---|
| 2021-06-04 11:39:41 +01:00 | 2021-06-04 13:39:41 EUROPE/HELSINKI | 10 | 13 | 13 |