Archives of the TeradataForum

Message Posted: Mon, 03 May 2004 @ 10:38:46 GMT


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

Subj:		Re: Converting time in miliseconds to TIEMSTAMP in teradata

From:		Victor Sokovin

For what it's worth, I came across an interesting bug while writing this solution. Multiplying intervals by a decimal number works correctly only for the hours, minutes, and seconds components. The fractional-second component does not multiply higher than the seconds component. To see what I mean, try the following SELECT:

     SELECT
         CAST('00:00:00.1' AS INTERVAL HOUR TO SECOND) A
         ,999 * A
         ,1000 * A

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.

     sel intrv_val
     , 999*intrv_val
     , 1000*intrv_val
     from A
     order by 1 ;

     intrv_val (999*intrv_val) (1000*intrv_val)

       0:00:00.001     0:00:00.999     0:00:01.000
       0:00:00.100     0:00:99.900     0:00:00.000
       0:00:00.990     0:00:89.010     0:00:90.000
       0:00:01.000     0:16:39.000     0:16:40.000
       0:00:01.100     0:16:38.900     0:16:40.000
       0:00:01.200     0:16:38.800     0:16:40.000
       0:00:01.900     0:16:38.100     0:16:40.000
       0:00:01.990     0:16:28.010     0:16:30.000
       0:00:01.999     0:16:37.001     0:16:39.000
       0:00:02.000     0:33:18.000     0:33:20.000


intrv_val is defined here as INTERVAL HOUR TO SECOND(3). As you see, the intrv_val arguments are given in asc order but the functions are
not at all ordered!


Regards,

Victor

 
 
 
 


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

Attachments

Library

Quick Reference