Issues of Datetime Data Types

------------------------------------------------------------------------------------------------------------------------

* Timestamp with local time zone

During the daylight saving time:

create table testfuturetime 
(id int, 
 d date, 
 t timestamp, 
 t_tz timestamp with time zone, 
 t_ltz timestamp with local time zone
);
insert into testfuturetime values 
(1, to_date('20161107 09:00:00','yyyymmdd hh24:mi:ss'),
 to_date('20161107 09:00:00','yyyymmdd hh24:mi:ss'),
 to_date('20161107 09:00:00','yyyymmdd hh24:mi:ss'),
 to_date('20161107 09:00:00','yyyymmdd hh24:mi:ss')
);

col t for a30
col t_tz for a35
col t_ltz for a30
select * from testfuturetime;

ID D                 T                              T_TZ                                T_LTZ
-- ----------------- ------------------------------ ----------------------------------- -----------------------------
 1 20161107 09:00:00 07-NOV-16 09.00.00.000000 AM   07-NOV-16 09.00.00.000000 AM -05:00 07-NOV-16 09.00.00.000000 AM

After the daylight saving ends:

ID D                 T                              T_TZ                                T_LTZ
-- ----------------- ------------------------------ ----------------------------------- ------------------------------
 1 20161107 09:00:00 07-NOV-16 09.00.00.000000 AM   07-NOV-16 09.00.00.000000 AM -05:00 07-NOV-16 08.00.00.000000 AM

Note the different time for the last field, timestamp with local time zone.

Ref: http://docs.oracle.com/database/121/NLSPG/ch4datetime.htm 

"Use the TIMESTAMP WITH TIME ZONE data type when the datetime value represents a future local time or the time zone information must be recorded with the value. Consider a scheduled appointment in a local time. The future local time may need to be adjusted if the time zone definition, such as daylight saving rule, changes. Otherwise, the value can become incorrect. This data type is most immune to such impact."

------------------------------------------------------------------------------------------------------------------------

* Get readable time given seconds since epoch

--assume current timezone is CDT (change as needed)
select new_time(to_date(to_char(&seconds_epoch/86400+to_date('19700101','yyyymmdd'),'yyyymmdd hh24:mi:ss'),'yyyymmdd hh24:mi:ss'), 'GMT', 'CDT') from dual;
--e.g. used on x$bh.tim
select new_time(to_date(to_char(tim/86400+to_date('19700101','yyyymmdd'),'yyyymmdd hh24:mi:ss'),'yyyymmdd hh24:mi:ss'), 'GMT', 'CDT') from x$bh;

------------------------------------------------------------------------------------------------------------------------

* Find time on remote database through DB link

--caution: undocumented
select sys.kupf$file.get_formatted_time@remotedb from dual;
--caution: may need license for tuning pack (credit to Tanel Poder)
select sys.dbms_sqltune_util1.get_current_time@remotedb from dual;

Ref: http://www.freelists.org/post/oracle-l/Current-Timestamp-over-DB-Link,8