Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 13 Feb 2004 @ 15:25:51 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Conversion of milli seconds value to timestamp
From:   Victor Sokovin


  In one of the process, the timestamp value has been converted to milliseconds taking reference as 1900-01-01. In the upstream process it is required to covert it back to timestamp value. May you please suggest what will be the best way to do in teradata side.  

I understand your format as the number of milliseconds as counted from the base date of Jan 1 1900, 00:00:00.000. If that is right, read on; otherwise, provide more explanation and examples.

If you want to convert that format to TIMESTAMP within Teradata you could first load the data as DECIMAL(13). Then calculate the whole number of days in each value dividing it by 24*3600*1000. Having that day you could lookup the date using the TD system calendar, i.e., the SYS_CALENDAR.CALENDAR view. DAY_OF_CALENDAR column gives you exactly the number of Julian days since Jan 1 1900. That's the lucky coincidence which should simplify your calculations a lot.

You still have the remainder left after the division. It gives you the number of milliseconds elapsed in the next day after the day you looked up above. It should not be too difficult to finish the conversion now. If you don't want to write the formulae you could perhaps use INTERVAL HOURS(2) TO SECONDS(3) or something like that?

Possible catches: check whether 24*3600*1000 is OK for all days in this period. Perhaps there were exceptions like some corrections, summer and winter time changes etc in some years. They could lead to some differences between the TD Julian days and the method you used earlier with your own conversion. The easiest way would be to take some test timestamps well spread across the whole period and convert them back and forth.



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