Question

我试图绕过两个日期,期望一些浮动价值回来。但我回过来的是:

+000000000 00:00:07.225000

价值86400的倍数(我想在第二段中有所区别)正在得到更加奇怪的回报:

+000000007 05:24:00.000000000

任何想法? 我的怀疑与投放类型有关。

Answer 1

页: 1 页: 1

减去时间序列的结果是interval。减去<代码>后栏数为两个日期之间的天数。

This is documented in the manual:
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;

<><><>><>>><>>>>>

If you want to convert the interval so e.g. the number of seconds (as a number), you can do something like this:

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。

Answer 2

Read this article: 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

友情链接