Home » SQL & PL/SQL » SQL & PL/SQL » Inconsistency in Interval Date Seconds handling? (11g version 11.1.0.6.0 (Win2003 on x64 and Linux on 2.6 x64))
icon5.gif  Inconsistency in Interval Date Seconds handling? [message #439850] Tue, 19 January 2010 13:00 Go to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4616
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 #439873 is a reply to message #439850] Tue, 19 January 2010 15:56 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
parenthesis matter; though mathematically they should not

  1* select EXTRACT(DAY FROM(TO_TIMESTAMP('01-JUL-08 07:52:03AM')-TO_DATE('01-JAN-08'))*24*60*60) from dual
SQL> /

EXTRACT(DAYFROM(TO_TIMESTAMP('01-JUL-0807:52:03AM')-TO_DATE('01-JAN-08'))*24*60*
--------------------------------------------------------------------------------
									15753123

SQL> select EXTRACT(DAY FROM(TO_TIMESTAMP('01-JUL-08 07:52:03AM')-TO_DATE('01-JAN-08'))*(24*60*60)) from dual;

EXTRACT(DAYFROM(TO_TIMESTAMP('01-JUL-0807:52:03AM')-TO_DATE('01-JAN-08'))*(24*60
--------------------------------------------------------------------------------
									15749655

SQL> select EXTRACT(DAY FROM(TO_TIMESTAMP('01-JUL-08 07:52:03AM')-TO_DATE('01-JAN-08'))*(24*60)*60) from dual;

EXTRACT(DAYFROM(TO_TIMESTAMP('01-JUL-0807:52:03AM')-TO_DATE('01-JAN-08'))*(24*60
--------------------------------------------------------------------------------
									15753123

SQL> select EXTRACT(DAY FROM(TO_TIMESTAMP('01-JUL-08 07:52:03AM')-TO_DATE('01-JAN-08'))*43200*2) from dual;

EXTRACT(DAYFROM(TO_TIMESTAMP('01-JUL-0807:52:03AM')-TO_DATE('01-JAN-08'))*43200*
--------------------------------------------------------------------------------
									15753123
Re: Inconsistency in Interval Date Seconds handling? [message #439875 is a reply to message #439873] Tue, 19 January 2010 16:16 Go to previous messageGo to next message
ThomasG
Messages: 3185
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 Go to previous messageGo to next message
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 Go to previous message
ThomasG
Messages: 3185
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

Previous Topic: Numeric or value error
Next Topic: HTML TABLE OUTPUT FORMAT
Goto Forum:
  


Current Time: Tue Sep 27 19:32:27 CDT 2016

Total time taken to generate the page: 0.23122 seconds