By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select to_timestamp('2018-06-08T23:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') as ts,
from_tz(
to_timestamp('2018-06-08T23:00:00', 'YYYY-MM-DD"T"HH24:MI:SS'),
'+05:30'
) as tstz_local,
from_tz(
to_timestamp('2018-06-08T23:00:00', 'YYYY-MM-DD"T"HH24:MI:SS'),
'+05:30'
) at time zone 'UTC' as tstz_utc,
to_char(
from_tz(
to_timestamp('2018-06-08T23:00:00', 'YYYY-MM-DD"T"HH24:MI:SS'),
'+05:30'
) at time zone 'UTC',
'YYYY-MM-DD HH24:MI:SS.FF6'
) as str_utc
from dual;
| TS | TSTZ_LOCAL | TSTZ_UTC | STR_UTC |
|---|---|---|---|
| 08-JUN-18 23.00.00.000000000 | 08-JUN-18 23.00.00.000000000 +05:30 | 08-JUN-18 17.30.00.000000000 UTC | 2018-06-08 17:30:00.000000 |
select to_char(
sys_extract_utc(
from_tz(
to_timestamp('2018-06-08T23:00:00', 'YYYY-MM-DD"T"HH24:MI:SS'),
'+05:30'
)
), 'YYYY-MM-DD HH24:MI:SS.FF6') as str_utc
from dual;
| STR_UTC |
|---|
| 2018-06-08 17:30:00.000000 |