的Oracle SQL得到两个时间戳

问题描述：

event   start        end 
event147   22-JUL-18 11.55.02.000000000 23-JUL-18 12.45.12.000000000 
event148   24-JUL-18 13.12.45.000000000 25-JUL-18 18.33.05.000000000

以上是md_events表之间的日差，我需要事件147的结束和事件148的开始之间获得日差（没有时间部分），两者都是时间戳。的Oracle SQL得到两个时间戳

我都试过了，但得到的错误（同样与numtodsinterval）

select dsintervaltonum(me2.start- me1.end, 'day') as days 
    from md_events me1, md_events me2 
where me1.event = 'event147' 
    and me2.event = 'event148'

此查询返回时间部分以及

select to_char(me2.start- me1.end, 'DDD') as days 
     from md_events me1, md_events me2 
    where me1.event = 'event147' 
     and me2.event = 'event148'

答

投两个时间戳的日期，然后直接把他们的区别：

select 
    cast(me2.start as date) - cast(me1.end as date) 
from md_events me1, md_events me2 
where 
    me1.event = 'event147' and 
    me2.event = 'event148'

谢谢，它的工作。 –

答

我的系统中没有Oracle。所以我要给出一种方法/逻辑来使用SQLITE来查找您的解决方案。用这种逻辑改变ORACLE语法的SQLITE语法!!

SELECT y.start,x.endd, y.start-x.endd as diff FROM (SELECT a.* FROM NAMES AS a WHERE a.event='event147') AS x, (SELECT b.* FROM NAMES AS b WHERE b.event='event148') AS y;

答

您也可以使用这一个：

SELECT EXTRACT(DAY FROM (me2.start- me1.end)) 
FROM md_events me1 
    CROSS JOIN md_events me2 
where 
    me1.event = 'event147' and 
    me2.event = 'event148'

的Oracle SQL得到两个时间戳

相关推荐