Database selected.

create temp table a (dt1 datetime year to second, dt2 datetime year to second, num interval year to month);
Temporary table created.


insert into a values (current , '2009-10-12 15:23:11',null);
1 row(s) inserted.


insert into a values (current , '2002-10-12 15:23:11',null);
1 row(s) inserted.



select (dt1 - dt2)
    , cast(dt1 - dt2 as interval second(9) to second  )
    , (dt1 - dt2 ):: interval second(9) to second
from a ;

(expression)       (expression) (expression)

       50 01:53:49    4326829      4326829
     2607 01:53:49  225251629    225251629

2 row(s) retrieved.



Database closed.

Database selected.

create temp table a (dt1 datetime year to second, dt2 datetime year to second, num interval year to month);
Temporary table created.                                                                                   

insert into a values (current , '2009-10-12 15:23:11',null);
1 row(s) inserted.                                          


insert into a values (current , '2009-06-12 15:23:11',null);
1 row(s) inserted.                                          


insert into a values (current , '2009-12-02 15:23:11',null);
1 row(s) inserted.                                          

----------------------
-- The nice way...
----------------------
CREATE PROCEDURE intrvl_to_sec( p INTERVAL DAY(9) TO SECOND)
RETURNING interval second(9) to second;;                    
  DEFINE vStrParam varchar(20);;                            
  DEFINE vDAY integer;;                                     
  DEFINE vhour integer;;                                    
  DEFINE vmin integer;;                                     
  DEFINE vsec integer;;                                     
  DEFINE vres interval second(9) to second;;                
  --set debug file to '/tmp/x.out' ;                        
  --trace on;                                               
  LET vStrParam=p;;                                         
  LET vStrParam=TRIM(vStrParam);;                           
  LET vDAY=substr(vStrParam,1,2);;                          
  LET vhour=substr(vStrParam,-8,2);;                        
  LET vmin=substr(vStrParam,-5,2);;                         
  LET vsec=substr(vStrParam,-2,2);;                         
  LET vres=vsec units second + vmin units minute + vhour units hour + vday units day ;;
  RETURN vres ;;                                                                       
END PROCEDURE ;                                                                        
Routine created.                                                                       
;

----------------------
-- The ugly way...
----------------------
CREATE PROCEDURE intrvl_to_sec2( p INTERVAL DAY(9) TO second)
RETURNING INTEGER;;                                          
  DEFINE vStrParam varchar(20);;                             
  DEFINE vDAY integer;;                                      
  DEFINE vhour integer;;                                     
  DEFINE vmin integer;;                                      
  DEFINE vsec integer;;                                      
  DEFINE vres INTEGER ;;                                     
  --set debug file to '/tmp/x.out' ;                         
  --trace on;                                                
  LET vStrParam=p;;                                          
  LET vStrParam=TRIM(vStrParam);;                            
  LET vDAY=substr(vStrParam,1,2);;                           
  LET vhour=substr(vStrParam,-8,2);;                         
  LET vmin=substr(vStrParam,-5,2);;                          
  LET vsec=substr(vStrParam,-2,2);;                          
  LET vres=vsec ;;                                           
  LET vres=vres + (vmin * 60) ;;                             
  LET vres=vres + (vhour *60*60);;                           
  LET vres=vres + (vday *24*60*60);;                         
  RETURN vres ;;                                             
END PROCEDURE ;                                              
Routine created.                                             

;

----------------------
-- The magic way...
----------------------
CREATE PROCEDURE intrvl_to_sec3( p INTERVAL second(9) TO second)
RETURNING interval second(9) to second;;                        
  RETURN p ;;                                                   
END PROCEDURE ;                                                 
Routine created.                                                
;

select first 1 
       intrvl_to_sec(interval(100 00:00:00) day(4) to second) as day
      ,intrvl_to_sec(interval(1 00:00:00) day to second) as day     
      ,intrvl_to_sec(interval(0 01:00:00) day to second) as hour    
      ,intrvl_to_sec(interval(0 00:01:00) day to second) as minute  
      ,intrvl_to_sec(interval(0 00:00:10) day to second) as second  
from a ;                                                            
day        day        hour       minute     second     
    864000      86400       3600         60         10
1 row(s) retrieved.


select (dt1 - dt2) ,intrvl_to_sec(dt1 - dt2) from a ;
(expression)       (expression) 
       51 02:19:06    4414746
      173 02:19:06    1477146
        0 02:19:06       8346
3 row(s) retrieved.

select first 1 
       intrvl_to_sec2(interval(100 00:00:00) day(4) to second) as day
      ,intrvl_to_sec2(interval(1 00:00:00) day to second) as day     
      ,intrvl_to_sec2(interval(0 01:00:00) day to second) as hour    
      ,intrvl_to_sec2(interval(0 00:01:00) day to second) as minute  
      ,intrvl_to_sec2(interval(0 00:00:10) day to second) as second  
from a ;                                                             

        day         day        hour      minute      second 
     864000       86400        3600          60          10
1 row(s) retrieved.

select (dt1 - dt2) ,intrvl_to_sec2(dt1 - dt2) from a ;
(expression)       (expression) 
       51 02:19:06      4414746
      173 02:19:06      1477146
        0 02:19:06         8346
3 row(s) retrieved.

select first 1 
       intrvl_to_sec3(interval(100 00:00:00) day(4) to second) as day
      ,intrvl_to_sec3(interval(1 00:00:00) day to second) as day     
      ,intrvl_to_sec3(interval(0 01:00:00) day to second) as hour    
      ,intrvl_to_sec3(interval(0 00:01:00) day to second) as minute  
      ,intrvl_to_sec3(interval(0 00:00:10) day to second) as second  
from a ;                                                             
day        day        hour       minute     second     
   8640000      86400       3600         60         10
1 row(s) retrieved.

select (dt1 - dt2) ,intrvl_to_sec3(dt1 - dt2) from a ;
(expression)       (expression) 
       51 02:19:06    4414746
      173 02:19:06   14955546
        0 02:19:06       8346
3 row(s) retrieved.

Database closed.