计算Oracle SQL中两个日期/时间的差异。

I have a table as follows:

我有一张表如下:

Filename - varchar
Creation Date - Date format dd/mm/yyyy hh24:mi:ss
Oldest cdr date - Date format dd/mm/yyyy hh24:mi:ss

How can I calcuate the difference in hours minutes and seconds (and possibly days) between the two dates in Oracle SQL?

在Oracle SQL的两个日期之间，我如何才能计算出两个日期之间的时间分和秒(甚至可能是几天)的差异?

Thanks

谢谢

15 个解决方案

#1

You can substract dates in Oracle. This will give you the difference in days. Multiply by 24 to get hours, and so on.

您可以在Oracle中减去日期。这会给你几天的不同。乘以24小时，以此类推。

SQL> select oldest - creation from my_table;

If your date is stored as character data, you have to convert it to a date type first.

如果您的日期存储为字符数据，则必须首先将其转换为日期类型。

SQL> select 24 * (to_date('2009-07-07 22:00', 'YYYY-MM-DD hh24:mi') 
             - to_date('2009-07-07 19:30', 'YYYY-MM-DD hh24:mi')) diff_hours 
       from dual;

DIFF_HOURS
----------
       2.5

Note:

注意:

This answer applies to dates represented by the Oracle data type DATE. Oracle also has a data type TIMESTAMP, which can also represent a date (with time). If you subtract TIMESTAMP values, you get an INTERVAL; to extract numeric values, use the EXTRACT function.

这个答案适用于由Oracle数据类型日期表示的日期。Oracle也有一个数据类型的时间戳，它也可以表示一个日期(随时间)。如果减去时间戳值，就得到一个区间;要提取数值，请使用extract函数。

#2

declare
strTime1 varchar2(50) := '02/08/2013 01:09:42 PM';
strTime2 varchar2(50) := '02/08/2013 11:09:00 PM';
v_date1 date := to_date(strTime1,'DD/MM/YYYY HH:MI:SS PM');
v_date2 date := to_date(strTime2,'DD/MM/YYYY HH:MI:SS PM');
difrence_In_Hours number;
difrence_In_minutes number;
difrence_In_seconds number;
begin
    difrence_In_Hours   := (v_date2 - v_date1) * 24;
    difrence_In_minutes := difrence_In_Hours * 60;
    difrence_In_seconds := difrence_In_minutes * 60;

    dbms_output.put_line(strTime1);        
    dbms_output.put_line(strTime2);
    dbms_output.put_line('*******');
    dbms_output.put_line('difrence_In_Hours  : ' || difrence_In_Hours);
    dbms_output.put_line('difrence_In_minutes: ' || difrence_In_minutes);
    dbms_output.put_line('difrence_In_seconds: ' || difrence_In_seconds);        
end ;

Hope this helps.

希望这个有帮助。

#3

select 
    extract( day from diff ) Days, 
    extract( hour from diff ) Hours, 
    extract( minute from diff ) Minutes 
from (
        select (CAST(creationdate as timestamp) - CAST(oldcreationdate as timestamp)) diff   
        from [TableName] 
     );

This will give you three columns as Days, Hours and Minutes.

这将给你3个栏目，时间，小时和分钟。

#4

To get result in seconds:

在几秒钟内得到结果:

select (END_DT - START_DT)*60*60*24 from MY_TABLE;

Check [https://community.oracle.com/thread/2145099?tstart=0][1]

检查[https://community.oracle.com/thread/2145099?tstart=0][1]

#5

Calculate age from HIREDATE to system date of your computer

计算年龄从HIREDATE到系统日期的计算机。

SELECT HIREDATE||'        '||SYSDATE||'       ' ||
TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) ||' YEARS '||
TRUNC((MONTHS_BETWEEN(SYSDATE,HIREDATE))-(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)*12))||
'MONTHS' AS "AGE  "  FROM EMP;

#6

You could use to_timestamp function to convert the dates to timestamps and perform a substract operation.

您可以使用to_timestamp函数将日期转换为时间戳，并执行substract操作。

Something like:

喜欢的东西:

SELECT 
TO_TIMESTAMP ('13.10.1990 00:00:00','DD.MM.YYYY HH24:MI:SS')  - 
TO_TIMESTAMP ('01.01.1990:00:10:00','DD.MM.YYYY:HH24:MI:SS')
FROM DUAL

#7

You may also try this:

你也可以试试:

select to_char(to_date('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')+(end_date - start_date),'hh24:mi:ss')
       as run_time from some_table;

It displays time in more human readable form, like: 00:01:34. If you need also days you may simply add DD to last formatting string.

它显示了更多人类可读形式的时间，比如:00:01:34。如果您还需要几天，您可以简单地将DD添加到最后的格式化字符串。

#8

select days||' '|| time from (
SELECT to_number( to_char(to_date('1','J') +
    (CLOSED_DATE - CREATED_DATE), 'J') - 1)  days,
   to_char(to_date('00:00:00','HH24:MI:SS') +
      (CLOSED_DATE - CREATED_DATE), 'HH24:MI:SS') time
 FROM  request  where REQUEST_ID=158761088 );

#9

If you want something that looks a bit simpler, try this for finding events in a table which occurred in the past 1 minute:

如果您想要看起来简单一些的东西，可以尝试在过去1分钟内发生的表中查找事件:

With this entry you can fiddle with the decimal values till you get the minute value that you want. The value .0007 happens to be 1 minute as far as the sysdate significant digits are concerned. You can use multiples of that to get any other value that you want:

有了这个条目，你就可以摆弄十进制值，直到你得到你想要的分钟值。值。0007恰好是1分钟，就像sysdate的有效数字一样。你可以用它的倍数来得到你想要的其他值:

select (sysdate - (sysdate - .0007)) * 1440 from dual;

Result is 1 (minute)

结果是1(分钟)

Then it is a simple matter to check for

这是一个很简单的问题。

select * from my_table where (sysdate - transdate) < .00071;

#10

$sql="select bsp_bp,user_name,status,
to_char(ins_date,'dd/mm/yyyy hh12:mi:ss AM'),
to_char(pickup_date,'dd/mm/yyyy hh12:mi:ss AM'),
trunc((pickup_date-ins_date)*24*60*60,2),message,status_message 
from valid_bsp_req where id >= '$id'";

#11

This will count time between to dates:

这将计算时间间隔:

SELECT
  (TO_CHAR( TRUNC (ROUND(((sysdate+1) - sysdate)*24,2))*60,'999999')
  +
  TO_CHAR(((((sysdate+1)-sysdate)*24)- TRUNC(ROUND(((sysdate+1) - sysdate)*24,2)))/100*60 *100, '09'))/60
FROM dual

#12

Here's another option:

这是另一个选择:

with tbl_demo AS
    (SELECT TO_DATE('11/26/2013 13:18:50', 'MM/DD/YYYY HH24:MI:SS') dt1
   , TO_DATE('11/28/2013 21:59:12', 'MM/DD/YYYY HH24:MI:SS') dt2 
     FROM dual)
SELECT dt1
     , dt2
     , round(dt2 - dt1,2) diff_days
     , round(dt2 - dt1,2)*24 diff_hrs
     , numtodsinterval((dt2 - dt1),'day') diff_dd_hh_mm_ss
  from tbl_demo;

#13

select (floor(((DATE2-DATE1)*24*60*60)/3600)|| ' : ' ||floor((((DATE2-DATE1)*24*60*60) -floor(((DATE2-DATE1)*24*60*60)/3600)*3600)/60)|| '  ' ) as time_difference from TABLE1

#14

in oracle 11g

在oracle 11 g

select end_date - start_date as day_diff from tablexxx suppose the starT_date end_date is define in the tablexxx

选择end_date - start_date作为day_diff来自tablexxx，假设start_date end_date在tablexxx中定义。

#15

(TO_DATE(:P_comapre_date_1, 'dd-mm-yyyy hh24:mi') - TO_DATE(:P_comapre_date_2, 'dd-mm-yyyy hh24:mi'))*60*60*24 sum_seconds,
         (TO_DATE(:P_comapre_date_1, 'dd-mm-yyyy hh24:mi') - TO_DATE(:P_comapre_date_2, 'dd-mm-yyyy hh24:mi'))*60*24 sum_minutes,
         (TO_DATE(:P_comapre_date_1, 'dd-mm-yyyy hh24:mi') - TO_DATE(:P_comapre_date_2, 'dd-mm-yyyy hh24:mi'))*24 sum_hours,
         (TO_DATE(:P_comapre_date_1, 'dd-mm-yyyy hh24:mi') - TO_DATE(:P_comapre_date_2, 'dd-mm-yyyy hh24:mi')) sum_days

#1