Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Thu, 10 Feb 2005 @ 06:12:21 GMT


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


Subj:   Re: Epoch function
 
From:   Dennis Calkins

On Wed, 9 Feb 2005 13:07:53 -0800, Ganga Palakattil wrote:

  Does Teradata have any epoch functions?  


  I have an absolute time value in milliseconds starting from 1970-01-01 00:00:00. I need to add that absolute time value to the base (1970- 01-01 00:00:00) and want to re-produce the real time-stamp.  



the simplest ( short of writing a UDF ) to do this is....

Divide and mod and build your own timestamp(6).


Sorry about the

(cast ((millisecs / 1000 ) as integer))


but Millisecs as a number is too big for an integer so Teradata Automatically makes it a float which causes rounding errors on some of the calculations.

The / 1000 makes the value seconds which is easier to operate on throughout the calculation.

To be consistent I used that notation for seconds every where.

     sel  1107982900000 millisecs,
      cast (
         (( date '1970-01-01' +
         ( (cast ((millisecs / 1000 )  as integer))
                 /  86400 ) ) ( format 'yyyy-mm-dd'))
                || ' ' ||
           ((((cast ((millisecs / 1000 )  as integer))
                  mod 86400) / 3600 ) (format '99' ))
                   || ':' ||
           ((((cast ((millisecs / 1000 )  as integer))
                  mod 3600 )  / 60) (format '99' ))
                   || ':' ||
           ((((cast ((millisecs / 1000 )  as integer))
                      mod (60))) (format '99' ))
                   || '.' ||
           ((millisecs mod 1000) (format '999' ))
                   || '000'
                as timestamp(6) )  mydate
     ;


     
  <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: 28 Jun 2020