oracle sql查找时差（均为字符串时间） [英] oracle sql find time difference (both time in string)

问题描述

在我的数据库中，我在VARCHAR2（8）中都有两列START_TIME，END_TIME，我需要使用END_TIME- START_TIME来找到持续时间。

In my db i have 2 columns START_TIME, END_TIME, both in VARCHAR2(8), i need to find the duration by using END_TIME- START_TIME.

START_TIME | END_TIME | DURATION

 21:30:03    21:34:11   00:04:08

我想执行以下操作，该怎么做？

I want to do something like the following, how can i do that?

SELECT TO_DATE(END_TIME, 'HH24:MI:SS') AS END, 
       TO_DATE(START_TIME, 'HH24:MI:SS') AS START, 
       (END-START) AS duration FROM Table1;

推荐答案

您的设计有缺陷[strong] >。

You have a flawed design.

• 您永远不应将 datetime 值存储为 string 。


• DATE 始终同时具有日期和时间元素，因此无需单独存储时间元素。

• You should never store a datetime value as string.
• DATE always has both date and time elements, so no need to store the time element separately.

无论如何，作为一种解决方法，您需要首先将字符串转换为日期，然后在 seconds 中找到持续时间，将秒数添加到 TRUNC（SYSDATE） ，那么其余的操作就是简单的日期格式。

Anyway, as a workaround, you need to first convert the string into date and then find the duration in seconds, add the seconds to TRUNC(SYSDATE), then the rest is simple date formatting.

例如，

SQL> WITH sample_data AS(
  2  SELECT '21:30:03' START_TIME, '21:34:11' END_TIME FROM dual
  3  )
  4  SELECT start_time,
  5    end_time,
  6    TO_CHAR (TRUNC (SYSDATE) + (to_date(end_time, 'HH24:MI:SS') -
  7                                to_date(start_time, 'HH24:MI:SS')
  8                               ) , 'hh24:mi:ss' ) duration
  9  FROM sample_data;

START_TI END_TIME DURATION
-------- -------- --------
21:30:03 21:34:11 00:04:08

SQL>

这篇关于oracle sql查找时差（均为字符串时间）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！