Inconsistency in Interval Date Seconds handling? [message #439850] |
Tue, 19 January 2010 13:00  |
tsouder
Messages: 2 Registered: January 2010
|
Junior Member |
|
|
There appears to be an inconsistency between the way that time intervals are multipled by numbers. For example, to calculate the number of seconds between 01-Jul-08 at 7:52:03 am and 01-Jan-08, one can:
SQL> EXTRACT(DAY FROM(TO_TIMESTAMP('01-JUL-08 07:52:03AM')-TO_DATE('01-JAN-08'))*24*60*60);
EXTRACT(DAYFROM(TO_TIMESTAMP('01-JUL-0807:52:03AM')-TO_DATE('01-JAN-08'))*24*60*60)
-----------------------------------------------------------------------------------
15753123
But if instead of using *24*60*60, one uses *86400, the answer changes:
SQL> EXTRACT(DAY FROM(TO_TIMESTAMP('01-JUL-08 07:52:03AM')-TO_DATE('01-JAN-08'))*86400)
EXTRACT(DAYFROM(TO_TIMESTAMP('01-JUL-0807:52:03AM')-TO_DATE('01-JAN-08'))*86400)
--------------------------------------------------------------------------------
15749655
If one multiples out the date interval and converts into second, the first answer appears to be correct:
SQL> select TO_TIMESTAMP('01-JUL-0807:52:03AM')-TO_DATE('01-JAN-08') from dual;
TO_TIMESTAMP('01-JUL-0807:52:03AM')-TO_DATE('01-JAN-08')
---------------------------------------------------------------------------
+000000182 07:52:03.000000000
SQL> select (((182*24)+07)*60+52)*60+3 from dual;
(((182*24)+07)*60+52)*60+3
--------------------------
15753123
Does anyone know why this is so? If it makes a difference my current timezone is -0500.
Thank you in advance for your help,
Tim
[Updated on: Tue, 19 January 2010 13:14] Report message to a moderator
|
|
|
Re: Inconsistency in Interval Date Seconds handling? [message #439870 is a reply to message #439850] |
Tue, 19 January 2010 15:36   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Probably rounding. Look:
FOO SCOTT>l
1* select to_date('01-JUL-08 07:52:03AM','dd-mon-rr hh:mi:ssam') -TO_DATE('01-JAN-2008','dd-mon-yyyy') from dual
FOO SCOTT>/
TO_DATE('01-JUL-0807:52:03AM','DD-MON-RRHH:MI:SSAM')-TO_DATE('01-JAN-2008','DD-MON-YYYY')
-----------------------------------------------------------------------------------------
182.327813
Multiplying that by one number (86400) would give you different results than multiplying by three numbers (24, 60 and 60 again) depending on how many significant digits you use.
Not that it makes a difference (but it might), you need to give DATE formats when you use things like TO_DATE. Just using TO_DATE would mean nothing without a format.
FOO SCOTT>l
1* select to_date('01-JAN-08') from dual
FOO SCOTT>/
select to_date('01-JAN-08') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
[Updated on: Wed, 20 January 2010 08:03] Report message to a moderator
|
|
|
|
Re: Inconsistency in Interval Date Seconds handling? [message #439875 is a reply to message #439873] |
Tue, 19 January 2010 16:16   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
It might also matter that the multiplication is actually still inside the parenthesis of the EXTRACT function. (At least that's how it looks to me, I might be squinting the wrong way, though)
Basically, since the syntax is like :
EXTRACT(day from date_or_timetamp)
Then it would be like:
EXTRACT(day from date_or_timetamp * 86400)
So you multiplicate the timestamp, which might be handled by Oracle in funny ways, since it perhaps switches from "day to second" to "year to month" intervals or something internally.
[Updated on: Tue, 19 January 2010 16:34] Report message to a moderator
|
|
|
Re: Inconsistency in Interval Date Seconds handling? [message #439983 is a reply to message #439873] |
Wed, 20 January 2010 07:55   |
tsouder
Messages: 2 Registered: January 2010
|
Junior Member |
|
|
It is interesting that the answer changes to the incorrect answer when the three multiplications are explicitly grouped. Checking the numbers, the correct answer seems to be (((182*24)+07)*60+52)*60+3 = 15753123. Even with decimal days, as stated by joy_division, 182.327813, it is 15753123.0432 seconds.
Why do you think that grouping the multiplication (24*60*60) should result in an incorrect answer? Do you think I should post a bug report to Oracle?
Thank you again for all of your help,
Tim
|
|
|
Re: Inconsistency in Interval Date Seconds handling? [message #440012 is a reply to message #439983] |
Wed, 20 January 2010 10:31  |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Well, accoding to PL/SQL Data Types
Quote:
Avoiding Truncation Problems Using Date and Time Subtypes
The default precisions for some of the date and time types are less than the maximum precision. For example, the default for DAY TO SECOND is DAY(2) TO SECOND(6), while the highest precision is DAY(9) TO SECOND(9). To avoid truncation when assigning variables and passing subprogram parameters of these types, you can declare variables and subprogram parameters of the following subtypes, which use the maximum values for precision:
So it would seem that when you have more than 99 days, Oracle might switch to the "year to month" interval by default during arithmetical operations.
For what you are trying to do it might be easier to just cast everything to date first, since then you can multiply the actual fractional days returned, you don't have to multiply timestamps.
Like:
SQL> select (CAST (TO_TIMESTAMP('01-JUL-08 07:52:03AM') AS DATE )
2 - TO_DATE('01-JAN-08')) *24*60*60 col from dual;
col
--------------
15753123
SQL>
SQL>
SQL> select (TO_DATE('01-JUL-08 07:52:03AM', 'dd-mon-yy hh:mi:ssAM')
2 - TO_DATE('01-JAN-08')) *24*60*60 col from dual;
---------------
15753123
[Updated on: Wed, 20 January 2010 10:31] Report message to a moderator
|
|
|