target database : Oracle Database 11g Enterprise Edition Release 22.214.171.124.0 - 64bit
TimeZone : US Central
I have a source data base (which is in Universal Time Zone ) from which i extract data and put in a oracle database(central time zone) . The problem that i have is when ever i extract something and put in the warehouse i have to subtract created date in the source table either 5hrs or 6hrs based on the off set time. This can be easily achieved by simply selecting the
[b]SELECT abs(TO_NUMBER(TO_CHAR(systimestamp,'TZH'))) FROM DUAL;[/b]
insert into target_table
select create_date - offset_time/24 from source_table ;
however how do i have to handle the off time if i have to go back in history to populate the data in to my target table from source table with out hard coding the offset time.