The problem here is to get two randomly chosen time zones from first distinct five, where unique key is pair time zone name and its offset. I have developed following solution. In my opinion and after some testing looks like working one. But the query itself looks little overcomplicated. Could you take a look and provide feedback if there is anything that could be improved?
SELECT
a as current_tz,
b as new_tz,
LOCALTIMESTAMP,
FROM_TZ(LOCALTIMESTAMP,a) AS withtimezone,
SYS_EXTRACT_UTC(FROM_TZ(LOCALTIMESTAMP,a)) AS extracted_utc,
FROM_TZ(LOCALTIMESTAMP,a) AT TIME ZONE b AS converted
FROM (
SELECT
*
FROM (
SELECT
ROWNUM AS rown,
t.*
FROM (
SELECT
tzoffset
FROM (
SELECT
DISTINCT
tzname,
TZ_OFFSET(tzname) AS tzoffset
FROM
v$timezone_names
ORDER BY
tzname
)
WHERE ROWNUM < 6
ORDER BY DBMS_RANDOM.VALUE
) t
WHERE ROWNUM < 3
)
PIVOT (
MAX(tzoffset) FOR rown IN (1 AS a, 2 AS b)
)
);