mySQL

最新推荐文章于 2025-08-06 16:23:50 发布

原创最新推荐文章于 2025-08-06 16:23:50 发布 · 159 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

Oracle 专栏收录该内容

1 篇文章

订阅专栏

本文探讨了Oracle数据库中使用DATEDIFF函数结合TIMESTAMP和TO_CHAR进行日期时间间隔计算的方法，以及如何将时间间隔转换为秒和分钟的实用技巧。通过具体的SQL查询示例，展示了如何高效地处理日期时间数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

datediff in Oracle

WITH T as (
  SELECT 
    starttime,
    endtime,
    TO_TIMESTAMP(
      TO_CHAR(endtime, 'dd-mon-yyyy hh24:mi:ss'),
      'dd-mon-yyyy hh24:mi:ss') - starttime as d
    FROM xx
    WHERE create_date > to_date(12-Dec-2012', 'dd-mon-yyyy')
    ORDER BY create_date DESC
)
SELECT
  starttime as "Start Time",
  endtime as "End Time",
  EXTRACT (DAY FROM d)*24*60*60+
  EXTRACT (HOUR FROM d)*60*60+
  EXTRACT (MINUTE FROM d)*60+
  EXTRACT (SECOND FROM d) as "Duration in Second",
  (EXTRACT (DAY FROM d)*24*60*60+
   EXTRACT (HOUR FROM d)*60*60+
   EXTRACT (MINUTE FROM d)*60+
   EXTRACT (SECOND FROM d)
  )/60 as "Duration in Minute"
FROM T;

WITH TT AS (
  select 'A' as emp, '|' as lft, '|2' as rgt from dual
  union 
  select 'B' as emp, '2' as lft, '3' as rgt from dual
  union
  select 'C' as emp, '4' as lft, '||' as rgt from dual
  union
  select 'D' as emp, '5' as lft, '6' as rgt from dual
  union 
  select 'E' as emp, '7' as lft, '8' as rgt from dual
  union 
  select 'F' as emp, '9' as lft, '|0' as rgt from dual
)
 SELECT P2.* FROM TT P2, TT P1 WHERE P1.lft BETWEEN P2.lft AND P2.rgt AND P1.emp = 'C';