Re: Source for tim=nnnnn in 10046 trace
- From: De DBA <dedba@xxxxxxxxxx>
- To: Yong Huang <yong321@xxxxxxxxx>, Oracle-L@xxxxxxxxxxxxx
- Date: Tue, 27 Aug 2013 14:50:14 +1000
Yong,
Tim is merely the number of microseconds elapsed since 1/1/70 0:00 on the
system clock, DST does not effect that. Your test was on 23/8/13, which is in
the northern summer, so DST does apply to the local date/time, i.e. sysdate. As
a result in the EST/EDT zone, when tim is subtracted from sysdate it should
yield a 4 hour difference from the start of the epoch, rather than 5.
i.e. 1377285865823053 + T0 =~ '2013-08-23 15:24:25 EDT' =~ '2013-08-23 19:24:25
UTC'
=> T0(edt) =~ '2013-08-23 15:24:25' - 1377285865823053/ ( 86400 * 10^^6
) = '1969-12-31 19:59:59'
BUT
=> T0(utc) =~ '2013-08-23 19:24:25' - 1377285865823053/ ( 86400 * 10^^6
) = '1969-12-31 23:59:59'
The problem arises because the subtraction does not take the timezone of the
date into account. If your server uses UTC for the system clock (as most do),
tim is the number of microseconds in the UTC epoch. Sysdate is the time in the
local timezone for the database(EDT in this case). So when you subtract tim
from the local system date, the result will be out by the amount that your
local system date _currently_ differs from UTC, -4 hours in your test.
You can correct for this using the hours part of the value returned by
sessiontimezone function. For instance on my Linux box:
14:09:30 SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
================
+10:00
1 row selected.
14:09:45 SQL> ! perl -e 'print time(), "\n";' -- gives epoch in seconds
1377576600
14:10:00 SQL> select sysdate
, 1377576600/86400 as day_epoch
, sysdate - 1377576600/86400 as AEST
, sysdate - 1377576600/86400 - to_number( substr(
sessiontimezone, 1, 3 ) )/24 as UTC
from dual;
SYSDATE | day_epoch|AEST |UTC
==========================|==========|==========================|==========================
27-AUG-2013 14:10:32 |15944.1736|01-JAN-1970 10:00:32 |01-JAN-1970 00:00:32
1 row selected.
Of course if you are in a fractional zone, you'd have to use the minutes part
of sessiontimezone as well.
Cheers
Tony
On 27/08/13 12:04, Yong Huang wrote:
> Tony,
>
> I thought about that. But at the end of December or beginning of January,
> it's not summer. So DST won't be in effect.
>
> I also thought about when the US started it. According to
> http://en.wikipedia.org/wiki/Daylight_saving_time
> "It became widely adopted, particularly in North America and Europe starting
> in the 1970s as a result of the 1970s energy crisis."
> It didn't say exactly when. But the beginning of 1970 is barely in the 1970s.
> But of course the epoch time could be put back retrospectively, and when they
> did it, it happened to be in a summer.
>
> Yong Huang
>
> *From:* De DBA <dedba@xxxxxxxxxx>
> *To:* oracle-l@xxxxxxxxxxxxx; yong321@xxxxxxxxx
> *Sent:* Monday, August 26, 2013 8:47 PM
> *Subject:* Re: Source for tim=nnnnn in 10046 trace
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 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
Other related posts:
