Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 12 Jan 2005 @ 14:51:20 GMT


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


Subj:   Re: Interval Overflow Error
 
From:   Geoffrey Rommel

  I am trying to find the difference between two timestamps which results in seconds  


          SELECT ( CAST( CAST(CURRENT_TIMESTAMP AS CHAR(19) ) AS
          TIMESTAMP(0))- CAST( '1970-01-01 00:00:00' AS TIMESTAMP(0))  )
          DAY(4) TO SECOND

I assume that you are trying to compute the Unix timeval, which is the number of seconds since 1970-01-01 00:00:00. If so, the following calculation will do it. (Warning: As long as Teradata uses 32-bit integers, this algorithm will be good only until some time in 2036.) Of course, you can substitute any timestamp column for current_timestamp. You may also need to adjust for your local time zone. This would be an excellent candidate for a User-Defined Function.

     select
       /* Hark back to 1968-03-01, the last leap year before the Unix
          epoch, so that the leap day will come out at the end of the
          year rather than in the middle. */
      ((cast((case
        when extract(month from current_timestamp) >= 3 then
          extract(year from current_timestamp) - 1968
        else
          extract(year from current_timestamp) - 1969
       end) * 365.25001    as integer) +
      (case extract(month from current_timestamp)
       when 1   then 306
       when 2   then 337
       when 3   then 0
       when 4   then 31
       when 5   then 61
       when 6   then 92
       when 7   then 122
       when 8   then 153
       when 9   then 184
       when 10  then 214
       when 11  then 245
       when 12  then 275
      end ) + extract(day from current_timestamp) ) * 86400 )
      + (extract(hour from current_timestamp) * 3600)
      + (extract(minute from current_timestamp) * 60)
      + cast(extract(second from current_timestamp) as integer)
      - 58060800  /* Readjust to 1970-01-01 */
     ;


     
  <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