orale 时间差处理

select

to_char(TIME_RECEIVE_CALL, 'dd-mm-yyyy hh24:mi:ss') start_time,

to_char(TIME_RESPONSE_TO_SCENE, 'dd-mm-yyyy hh24:mi:ss') end_time,

(TIME_RESPONSE_TO_SCENE-TIME_RECEIVE_CALL)*24*60 c,

TIME_RECEIVE_CALL+40/(30*60) a,

months_between(TIME_RESPONSE_TO_SCENE,TIME_RECEIVE_CALL)*30*24*60 b

from FOUND_PROPERTY

where substr((TIME_RESPONSE_TO_SCENE-TIME_RECEIVE_CALL)*24*60,0,11) > '+000000020'


[color=red][b]Result[/b][/color]


14-06-2010 16:32:00 26-06-2010 12:12:00 [b][color=red]17020 0:0:0.0[/color] [/b]2010-06-14 17:04:00.0 [color=green]16470.9677419354838709677419354838709677[/color]

14-06-2010 17:35:00 14-06-2010 18:05:00 [color=red]30 0:0:0.0[/color] 2010-06-14 18:07:00.0 [color=green]0[/color]

14-06-2010 16:32:00 14-06-2010 17:12:00 [color=red]40 0:0:0.0[/color] 2010-06-14 17:04:00.0 [color=green]0[/color]

15-06-2010 12:27:00 15-06-2010 12:56:00 [color=red]29 0:0:0.0 [/color]2010-06-15 12:59:00.0 [color=green]0[/color]

14-06-2010 17:35:00 14-06-2010 18:56:00 [color=red]81 0:0:0.0 [/color]2010-06-14 18:07:00.0 [color=green]0[/color]

15-06-2010 17:13:00 14-06-2010 17:18:00 [color=darkred]-1435 0:0:0.0[/color] 2010-06-15 17:45:00.0 [color=olive]-1388.709677419354838709677419354838709676[/color]

14-06-2010 16:32:00 14-06-2010 17:12:00 [color=red]40 0:0:0.0 [/color]2010-06-14 17:04:00.0 [color=green]0[/color]

select substr((TIME_RESPONSE_TO_SCENE-TIME_RECEIVE_CALL)*24*60,0,11) from FOUND_PROPERTY
where substr((TIME_RESPONSE_TO_SCENE-TIME_RECEIVE_CALL)*24*60,1,11) <= '+000000021'

[color=violet]+000000004
+000000004
+000000004
+000000004
+000000004
+000000004 [/color]