Sep 232013
 
select an_easier_way, 
       to_adjust_for_daylight_savings
from   data_stuck_in_utc_time
where  case 
  when utc_datetime between 
    --clocks forward and back
    next_day(last_day(
      add_months(
        trunc(daylight_savings_datetime, 'y'),
        2)) - 7, 'sunday') and
    next_day(last_day(
      add_months(
        trunc(daylight_savings_datetime, 'y'),
        9)) - 7, 'sunday') 
  then 
    utc_datetime+1/24 -- an hour
  else 
    utc_datetime
  end = daylight_savings_datetime;

AN_EASIER_WAY
----------------------------
There is
TO_ADJUST_FOR_DAYLIGHT_SAVINGS
---------------------------------------
utc_datetime+
  extract(timezone_hour from 
          daylight_savings_datetime)/24
A classic convoluted build your own solution to a problem Oracle's 
already solved for you; figuring out the daylight savings offset 
for a particular date. This is the calculation for the UK (daylight 
savings starts the last Sunday in March and ends the last Sunday in 
October), but it varies in some countries. Must easier to have a 
timestamp with time zone and just extract the hour offset from it!

If you’d like a complete script showing this, keep reading.

create table data_stuck_in_utc_time (
  an_easier_way varchar2(10), 
  to_adjust_for_daylight_savings varchar2(100),
  utc_datetime  date,
  daylight_savings_datetime 
      timestamp with time zone
);

insert into data_stuck_in_utc_time 
values ('There is', 
  'utc_datetime+extract(timezone_hour 
    from daylight_savings_datetime)/24', 
  date'2013-01-01', 
  timestamp'2013-01-01 00:00:00 Europe/London'
);
insert into data_stuck_in_utc_time 
values ('There is', 
  'utc_datetime+extract(timezone_hour 
    from daylight_savings_datetime)/24', 
  date'2013-04-01', 
  timestamp'2013-04-01 01:00:00 Europe/London'
);
insert into data_stuck_in_utc_time 
values ('There is', 
  'utc_datetime+extract(timezone_hour 
    from daylight_savings_datetime)/24', 
  date'2013-09-01', 
  timestamp'2013-09-01 01:00:00 Europe/London'
);
insert into data_stuck_in_utc_time 
values ('There is',  
  'utc_datetime+extract(timezone_hour 
    from daylight_savings_datetime)/24', 
  date'2013-12-01', 
  timestamp'2013-12-01 00:00:00 Europe/London'
);

commit;

-- straight join, the daylight savings days 
-- aren't returned because they're an hour
-- ahead
select utc_datetime, 
       daylight_savings_datetime
from   data_stuck_in_utc_time
where  utc_datetime = daylight_savings_datetime;

-- the longhand approach
select an_easier_way, 
       to_adjust_for_daylight_savings
from   data_stuck_in_utc_time
where  case 
  when utc_datetime between 
    --clocks forward and back
    next_day(last_day(
      add_months(
        trunc(daylight_savings_datetime, 'y'),
        2)) - 7, 'sunday') and
    next_day(last_day(
      add_months(
        trunc(daylight_savings_datetime, 'y'),
        9)) - 7, 'sunday') 
  then 
    utc_datetime+1/24 -- an hour
  else 
    utc_datetime
  end = daylight_savings_datetime;

-- the easy approach
select utc_datetime, 
       daylight_savings_datetime
from   data_stuck_in_utc_time
where  utc_datetime+
  (extract(timezone_hour 
    from daylight_savings_datetime)/24) 
      = daylight_savings_datetime;

drop table data_stuck_in_utc_time purge;

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)