Archives of the TeradataForum

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

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

          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>>