world clock

with z as (
  select
  distinct
     tn.tzname as "timezone",
     cast(
        from_tz(
           cast(sysdate as timestamp),
           dbtimezone
        ) at time zone tn.tzname
        as date
     ) as "time there"
  from V$TIMEZONE_NAMES tn
)
select
   "time there",
   listagg("timezone", ', ') within group (order by "timezone") as "where"
from z
group by "time there"
order by "time there"

About these ads