Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 03 May 2004 @ 12:19:18 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Converting time in miliseconds to TIEMSTAMP in teradata
 
From:   Dieter Noeth

Victor Sokovin wrote:

  Thanks for the warning. I have looked at the calculations with fractional intervals on the table level (not literals) and the results look strange, to say the least.  



It's really strange, as soon as the milliseconds add to more than 1 second:

(Nums is a table with consecutive integers, V2R05.01.00.06)

     sel
        interval '0:00:01.999' hour to second as i,
        n (format 'zzz'),
        i * n  as x,
        sum(i) over (order by n rows unbounded preceding) as cum_sum,
        mdiff(x,1,n)
     from nums
     where n <= 100
     order by n;

       *** Query completed. 100 rows found. 5 columns returned.
       *** Total elapsed time was 1 second.

                i    n                x       cum_sum     MDiff(x,1,n)
     ------------  ---  ---------------  ------------  ---------------
.......
      0:00:01.999   29      0:00:57.971   0:00:57.971      0:00:01.999
      0:00:01.999   30      0:00:59.970   0:00:59.970      0:00:01.999
      0:00:01.999   31      0:00:61.969   0:01:01.969      0:00:01.999
      0:00:01.999   32      0:00:63.968   0:01:03.968      0:00:01.999
      0:00:01.999   33      0:00:65.967   0:01:05.967      0:00:01.999
.......
      0:00:01.999   49      0:00:97.951   0:01:37.951      0:00:01.999
      0:00:01.999   50      0:00:99.950   0:01:39.950      0:00:01.999
      0:00:01.999   51      0:00:01.949   0:01:41.949      0:00:01.999
      0:00:01.999   52      0:00:03.948   0:01:43.948      0:00:01.999
.......
      0:00:01.999   59      0:00:17.941   0:01:57.941      0:00:01.999
      0:00:01.999   60      0:01:59.940   0:01:59.940      0:00:01.999
      0:00:01.999   61      0:01:61.939   0:02:01.939      0:00:01.999
      0:00:01.999   62      0:01:63.938   0:02:03.938      0:00:01.999
     ...

The Cumulative Sum is correct.

Especially strange:

- MDiff is always returning the same difference.

- If you calculate the difference between cum_sum and x it's always 0.

- But if you "QUALIFY cum_sum <> 0" then every row with n >=31 is returned.


Don't ask me why ;-)

Dieter



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019