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

 < 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 */
;
```

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2005 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback