在Oracle中将时差转换为给定的格式 [英] Converting time difference to a given format in Oracle
问题描述
如何将 EVENT_DATE_B - EVENT_DATE_A
换算成 HH:MM
格式的天数? 如果日期只有时间部分不同,则可以使用间隔时间间隔。例如:
SQL>选择(to_date('25.12.12 15:37:32','DD.MM.YY HH24:MI:SS')
2 - to_date('25 .12.12 12:45:45','DD。 MM.YY HH24:MI:SS'))日(0)到第二(0)时间
3从双
4;
TIME
-------------
+0 02:51:47
但显然情况并非总是如此。所以你可以写一个长查询来计算不同的时间部分,但我想我会用这个简单的函数:
$ pre $ SQL $>> ;创建或替换函数DaysToTime(p_val in number)
2 return varchar2
3是
4 l_hours number;
5 l_minutes数字;
6 l_seconds数字;
7 begin
8 l_Hours:= 24 * p_val;
9 l_minutes:=(l_hours - trunc(l_hours))* 60;
10 l_seconds:=(l_minutes - trunc(l_minutes))* 60;
11 return to_char(trunc(l_hours),'fm09')||':'||
12 to_char(trunc(l_minutes),'fm09')||':'||
13 to_char(trunc(l_seconds),'fm09');
14 end;
15 /
创建的函数
现在查询会be:
SQL>选择DaysToTime(to_date('25.12.12 15:37:32','DD.MM.YY HH24:MI:SS')
2 - to_date('25 .12.12 12:45:45','DD .MM.YY HH24:MI:SS'))as
3 from
4;
TIME
----------
02:51:47
How do I convert EVENT_DATE_B - EVENT_DATE_A
which is a number of days to string with HH:MM
format?
If dates are differ only in time part you can use interval day to second. For instance:
SQL> select (to_date('25.12.12 15:37:32', 'DD.MM.YY HH24:MI:SS')
2 - to_date('25.12.12 12:45:45', 'DD.MM.YY HH24:MI:SS')) day(0) to second(0) as Time
3 from dual
4 ;
TIME
-------------
+0 02:51:47
But obviously it will not always be the case. So you could write a long query to calculate different parts of time, but I think I would go with this simple function:
SQL> create or replace function DaysToTime(p_val in number)
2 return varchar2
3 is
4 l_hours number;
5 l_minutes number;
6 l_seconds number;
7 begin
8 l_Hours := 24 * p_val;
9 l_minutes := (l_hours - trunc(l_hours)) * 60;
10 l_seconds := (l_minutes - trunc(l_minutes)) * 60;
11 return to_char(trunc(l_hours), 'fm09') ||':'||
12 to_char(trunc(l_minutes), 'fm09')||':'||
13 to_char(trunc(l_seconds), 'fm09');
14 end;
15 /
Function created
And now the query would be:
SQL> select DaysToTime(to_date('25.12.12 15:37:32', 'DD.MM.YY HH24:MI:SS')
2 - to_date('25.12.12 12:45:45', 'DD.MM.YY HH24:MI:SS')) as Time
3 from dual
4 ;
TIME
----------
02:51:47
这篇关于在Oracle中将时差转换为给定的格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!