Overview of all time changes this year via SQL

inspired by Laurent Schneider’s remark concerning special time changes in Lord Howe Island

with dates as
(
select
trunc(sysdate, 'year') + level - 1 as day
from
dual
connect by
extract(year from trunc(sysdate, 'year') + level - 1) = extract(year from trunc(sysdate, 'year'))
),

timezones as
(
select
vtn.TZNAME, listagg(vtn.TZABBREV, ', ') within group(order by vtn.tzabbrev) tzabbrevs
from
v$timezone_names vtn
group by
vtn.TZNAME
),

offsets as
(
select
v.*, d.day,
24 * (cast(from_tz(cast(d.day as timestamp), 'UTC') at time zone v.TZNAME as date) - d.day) as offset
from
timezones v, dates d
),

changes as
(
select
d.*, d.loffset - d.offset offset_change,
case when d.loffset > d.offset then 'DST start' else 'DST end' end as time_change
from
(
select
o.*, lead(o.offset) over(partition by o.tzname order by o.day) loffset
from offsets o
) d
where d.offset != d.loffset
)
select
c.tzname,
max(case c.time_change when 'DST start' then c.day end) as DST_start,
rtrim(to_char(max(case c.time_change when 'DST start' then c.offset_change end), 'S90.99'), '0.') || ' hour' as time_change_DST_start,
'UTC' || rtrim(to_char(max(case time_change when 'DST start' then c.loffset end), 'S90.99'), '0.') || ' hour' as offset_after_DST_Start,
max(case c.time_change when 'DST end' then c.day end) as DST_end,
rtrim(to_char(max(case c.time_change when 'DST end' then c.offset_change end), 'S90.99'), '0.') || ' hour' as time_change_DST_end,
'UTC' || rtrim(to_char(max(case time_change when 'DST end' then c.loffset end), 'S90.99'), '0.') || ' hour' as offset_after_DST_end,
c.tzabbrevs
from
changes c
group by
c.tzname, c.tzabbrevs
order by
dst_start, c.tzname