Sokrates on Oracle

Overview of all time changes this year via SQL

Posted by Matthias Rogel on 30. March 2015

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: