########################################################
#        name: fmt_interval.sql
#     version: 0.0
# description: 'select' statement that demonstrates
#              calculating an interval and displaying 
#              it in DD:HH24:MI format
#    synopsis: no arguments
#       usage: cut and paste to run at the SQL prompt
#    comments: A stupid SQL trick if I ever saw one, but
#              I guess this is what you have to do
#   copyright: David Clement (c) 2006 
#    warranty: No warranty express or implied
#    creation: 29 SEP 2006
#      author: David Clement
#     changes:
########################################################
select 
replace (to_char (extract (day from numtodsinterval 
      (max (xyz.last_timestamp) 
      - min (xyz.last_timestamp), 'day')), '09')
    || ':' 
    || to_char (extract (hour from numtodsinterval
      (max (xyz.last_timestamp) 
      - min (xyz.last_timestamp), 'day')), '09')
    || ':' 
    || to_char (extract (minute from numtodsinterval
      (max (xyz.last_timestamp) 
      - min (xyz.last_timestamp), 'day')), '09')
    || ':' 
    || to_char (extract (second from numtodsinterval
      (max (xyz.last_timestamp) 
      - min (xyz.last_timestamp), 'day')), '09'), ' ', '')
from some_table xyz