the 4 hours time difference also occurs in the UTC-5 zone (New York, Detroit, Miami) when Daylight Savings Time is in force. Cheers, Tony On 27/08/13 00:54, Yong Huang wrote: > One addition to what I posted earlier (shown below). The derived_sysdate in > the query is 1969-12-31 19:59:59, i.e. 8pm instead of 23:59:59 of that night > (or 12am of 1970-01-01). The 4 hour difference may be due to different > timezones. According to > http://www.timeanddate.com/worldclock/converted.html?iso700101T00&p1=0&p29&p3#&p4(3&p5(6 > > 8pm of 1969-12-31 at UTC epoch is the time for the timezone where Nova > Scotia, Canada is. I'm not sure why. > > Yong Huang > > My previous message: > > > You just need to change your sysdate to 2013-08-23 15:24:25.823. For example: > > SQL> with x as (select 1377285865823053 as t, (1377285865823053/1e6) as t1 > from dual) > 2 select t tim_micro, t1 tim_sec, '2013-08-23 15:24:25' curr_sysdate, > to_date('2013-08-23 15:24:25','yyyy-mm-dd hh24:mi:ss')-(t1/86400) > 3 derived_sysdate > 4 from x; > > TIM_MICRO TIM_SEC CURR_SYSDATE DERIVED_SYSDATE > -------------------- -------------------- ------------------- > ------------------- > 1377285865823053 1377285865.823053 2013-08-23 15:24:25 1969-12-31 > 19:59:59 > > The derived sysdate is the epoch. > -- > //www.freelists.org/webpage/oracle-l > > > -- //www.freelists.org/webpage/oracle-l