Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Getting two random timezones and calculating timestamp between them

foxhoundJul 16 2022

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)
)
);

Comments
Post Details
Added on Jul 16 2022
2 comments
119 views