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