Geek asks and answers

Subtracting timestamps in oracle returning strange data

I am trying to subtract two dates and waiting for the return of floating values. But what I got in return is as follows:

+000000000 00:00:07.225000

Multiplying the value by 86400 (I want to get the difference in the second) gets the returned stranger value:

+000000007 05:24:00.000000000

any idea? I suspect this has something to do with type.

+3
source share
2 answers

I think your columns are defined as timestamp, rather than date.

The result of subtracting timestamps is interval, while the result of subtracting the columns dateis a number representing the number of days between two dates.

:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#i48042

, , , :

with dates as (
   select timestamp '2012-04-27 09:00:00' as col1,
          timestamp '2012-04-26 17:35:00' as col2
   from dual
)
select col1 - col2 as ts_difference,
       cast(col1 as date) - cast(col2 as date) as dt_difference
from dates;

Edit:

, , ( ), - :

with dates as (
   select timestamp '2012-04-27 09:00:00.1234' as col1,
          timestamp '2012-04-26 17:35:00.5432' as col2
   from dual
)
select col1 - col2 as ts_difference,
       extract(hour from (col1 - col2)) * 3600 +  
       extract(minute from (col1 - col2)) * 60 + 
       (extract(second from (col1 - col2)) * 1000) / 1000 as seconds
from dates;

: 55499.5802

+6

: http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551242712657900129

:

create table yourtable(
date1 date, 
date2 date
)
SQL> select datediff( 'ss', date1, date2 ) seconds from yourtable

   SECONDS 
---------- 
   6269539
0

More articles:


All Articles