计算 Oracle SQL 中 2 个日期/时间之间的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1096853/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculate difference between 2 date / times in Oracle SQL
提问by Steve
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
谢谢
回答by Thilo
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 TIMESTAMPvalues, you get an INTERVAL; to extract numeric values, use the EXTRACTfunction.
此答案适用于 Oracle 数据类型表示的日期DATE。Oracle 还有一个数据类型TIMESTAMP，它也可以表示日期（带时间）。如果你减去TIMESTAMP值，你会得到一个INTERVAL; 要提取数值，请使用该EXTRACT函数。
回答by Mohsen Heydari
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.
希望这可以帮助。
回答by Nikolay Frick
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]
回答by Ishan Rastogi
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.
这将为您提供三列作为天数、小时数和分钟数。
回答by RLapinski
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 添加到最后一个格式化字符串。
回答by HyLian
You could use to_timestamp function to convert the dates to timestamps and perform a substract operation.
您可以使用 to_timestamp 函数将日期转换为时间戳并执行减法运算。
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
回答by SUNIL BEHERA
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;
回答by amitt Gaur
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 );
回答by Michael
In oracle 11g
在甲骨文 11g
SELECT end_date - start_date AS day_diff FROM tablexxx
suppose the starT_date end_date is define in the tablexxx
回答by Nik
If you select two dates from 'your_table' and want too see the result as a single column output (eg. 'days - hh:mm:ss') you could use something like this. First you could calculate the interval between these two dates and after that export all the data you need from that interval:
如果您从“ your_table”中选择两个日期并希望将结果视为单列输出（例如“ days - hh:mm:ss”），您可以使用这样的东西。首先，您可以计算这两个日期之间的间隔，然后从该间隔导出您需要的所有数据：
select extract (day from numtodsinterval (second_date
- add_months (created_date,
floor (months_between (second_date,created_date))),
'day'))
|| ' days - '
|| extract (hour from numtodsinterval (second_date
- add_months (created_date,
floor (months_between (second_date,created_date))),
'day'))
|| ':'
|| extract (minute from numtodsinterval (second_date
- add_months (created_date,
floor (months_between (second_date, created_date))),
'day'))
|| ':'
|| extract (second from numtodsinterval (second_date
- add_months (created_date,
floor (months_between (second_date, created_date))),
'day'))
from your_table
And that should give you result like this: 0 days - 1:14:55
这应该给你这样的结果： 0 天 - 1:14:55
