我的问题:我需要使用脚本以HH:MI:SS格式获取两个日期(时间)之间的差异。例如,如果我有两次:
2012/08/24 13:04:23
2012/08/24 14:15:32
我的结果是:
1:11:09
要求:我是使用SAP的BusinessObjects Data Services Designer编写这篇文章的,因此我的脚本必须满足它们的语法规则(单击此处查看长PDF文档…如果您不熟悉SAP的语法,只需知道它真的很有限…)。
我尝试过的内容:目前我有一个函数(见下文),它将为用户提供一个SQL语句,用户可以在SQL
函数中使用该语句,该函数将为他们提供所需内容,但我希望使此数据存储不可知。
这是我目前的功能(并不是我真正想要的):
#Use this with a SQL function. For example: SQL('DSS_USER', GetTimeDifferenceSQL($time_1, $time_2));
return '
with seconds as (
select 24*60*60*(to_date('[$time_1]', 'YYYY.MM.DD hh24:mi:ss')
- to_date('[$time_2]', 'YYYY.MM.DD hh24:mi:ss')) seconds_diff
from dual
)
select to_char(trunc(sum(seconds_diff)/3600), 'FM999999990') || ':' || to_char(trunc(mod(sum(seconds_diff),3600)/60), 'FM00') || ':' || to_char(mod(sum(seconds_diff),60), 'FM00')
from seconds
';
这不起作用,因为我希望它从数据存储中独立出来,并在脚本中单独计算。
我还尝试过将日期转换为十进制数字,然后对其使用JED_Time(int)
函数,这很有效,除了十进制是10,时间是10。。。所以这是行不通的。
我的阻碍:我很难过,因为时间并没有真正定义的基础。。。。任何帮助都会非常棒!谢谢
一个更简单的方法是使用数据类型Interval。
获取两个日期时间之间的秒数:
interval_to_char( $dtFrom - $dtTo , 'SS');
获取一个间隔并返回该间隔的字符表示形式。
interval_to_char([in]InputInterval As interval,[in]FormatString As varchar)As varchar
此函数还可以用于获取分钟和小时。
这可能完全准确,也可能不完全准确,但我想我明白了。非常欢迎输入。希望这有一天能帮助到别人!
#Make sure that when we're finding the difference, we always take the lesser date from the greater date. We'll negate it at the end.
if ($time_1 < $time_2)
begin
$temp_time = $time_1;
$time_1 = $time_2;
$time_2 = $temp_time;
end
#Get all value differences from the two times
$nanoseconds = to_decimal(to_char($time_1, 'FF'), '.', null, 0) - to_decimal(to_char($time_2, 'FF'), '.', null, 0);
$seconds = to_decimal(to_char($time_1, 'SS'), '.', null, 0) - to_decimal(to_char($time_2, 'SS'), '.', null, 0);
$minutes = to_decimal(to_char($time_1, 'MI'), '.', null, 0) - to_decimal(to_char($time_2, 'MI'), '.', null, 0);
$hours = to_decimal(to_char($time_1, 'HH24'), '.', null, 0) - to_decimal(to_char($time_2, 'HH24'), '.', null, 0);
$days = interval_to_char($time_1 - $time_2, 'D');
#fix nanoseconds
if ($nanoseconds >= 1000000000)
begin
$seconds = $seconds + 1;
$nanoseconds = $nanoseconds - 1000000000;
end
if ($nanoseconds < 0)
begin
$seconds = $seconds -1;
$nanoseconds = $nanoseconds + 1000000000;
end
#fix seconds
if ($seconds >= 60)
begin
$minutes = $minutes + 1;
$seconds = $seconds - 60;
end
if ($seconds < 0)
begin
$minutes = $minutes -1;
$seconds = $seconds + 60;
end
#fix minutes
if ($minutes >= 60)
begin
$hours = $hours + 1;
$minutes = $minutes - 60;
end
if ($minutes < 0)
begin
$hours = $hours -1;
$minutes = $minutes + 60;
end
#fix hours
if ($hours >= 24)
begin
$days = $days + 1;
$hours = $hours - 24;
end
if ($hours < 0)
begin
$days = $days - 1;
$hours = $hours + 24;
end
#fix days
if (trunc($days/365, 0) >= 1)
begin
$years = trunc($days/365, 0);
$days = $days - ($years * 365);
end
if (round($days/7, 0) > 0)
begin
$weeks = round($days/7, 0);
$days = $days - ($weeks * 7);
end
$ret = '';
if ($years > 0)
begin
$ret = $years||' year'||ifthenelse($years = 1, '', 's')||' ';
end
if ($weeks > 0)
begin
$ret = $ret||$weeks||' week'||ifthenelse($weeks = 1, '', 's')||' ';
end
if ($days > 0)
begin
$ret = $ret||$days||' day'||ifthenelse($days = 1, '', 's')||' ';
end
$ret = $ret||$hours||':'||lpad($minutes, 2, '0')||':'||lpad($seconds, 2, '0')||':'||lpad(round($nanoseconds/1000000, 0), 3, '0');
#Negate it if the parameter values were swapped at the beginning (using $temp_time)
if ($temp_time is not null)
begin
$ret = '-'||$ret;
end
return $ret;