Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2009
    Posts
    3

    Question Unanswered: Date math in SQL

    I am running a SQL that returns the interval between two datetimes. How do I control the precision of the returned value? I would like the interval to be returned as seconds(3). I have looked through the manual and can not find this answer.

    select (dt1 - dt2) as elapsed_tm from table;

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    Code:
    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.
    Last edited by ceinma; 12-01-09 at 15:19. Reason: add better example...
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Dec 2009
    Posts
    3
    @ceinma

    Thanks for your solution. It identified that I left out one important piece of information. I am still using Informix v7.31.

  4. #4
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311

    Wink there is...

    There several ways.. all tested in IDS 7.31 (I have a production with 7.31 too)

    Pick up any of the three, but I recommend the procedure intrvl_to_sec3.
    Code:
    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.
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  5. #5
    Join Date
    Dec 2009
    Posts
    3
    @ceinma

    Thanks for all your hard work and help!

    The "magic way" is definitely the direction I'll go. I suspect that it will be faster too.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •