Archives of the TeradataForum

Message Posted: Mon, 03 May 2004 @ 15:28:09 GMT


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

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

From:		Victor Sokovin

On Mon, 3 May 2004 06:38:46 -0400, 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.

     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.

It has just occurred to me to apply the following trick and it seems to be working for the interval test values I have had in my table. I don't have time to do more testing right now so I have no clue whether the trick will always work but I thought I should mention it for those who might be looking now for a quick workaround.

Dieter, if you are reading this: would this modification work in your test case?

     sel intrv_val
     , 999*intrv_val
     , 10*(999*intrv_val)/10
     , 1000*intrv_val
     , 10*(1000*intrv_val)/10
     from A
     order by 1;

      intrv_val (999*intrv_val) ((10*(999*intrv_val))/10)
      (1000*intrv_val) ((10*(1000*intrv_val))/10)

        0:00:00.001     0:00:00.999     0:00:00.999     0:00:01.000     0:00:01.000
        0:00:00.100     0:00:99.900     0:01:39.900     0:00:00.000     0:01:40.000
        0:00:00.990     0:00:89.010     0:16:29.010     0:00:90.000     0:16:30.000
        0:00:01.000     0:16:39.000     0:16:39.000     0:16:40.000     0:16:40.000
        0:00:01.100     0:16:38.900     0:18:18.900     0:16:40.000     0:18:20.000
        0:00:01.200     0:16:38.800     0:19:58.800     0:16:40.000     0:20:00.000
        0:00:01.900     0:16:38.100     0:31:38.100     0:16:40.000     0:31:40.000
        0:00:01.990     0:16:28.010     0:33:08.010     0:16:30.000     0:33:10.000
        0:00:01.999     0:16:37.001     0:33:17.001     0:16:39.000     0:33:19.000
        0:00:02.000     0:33:18.000     0:33:18.000     0:33:20.000     0:33:20.000

Regards,

Victor


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

Attachments

Library

Quick Reference