 Analytics Welcome to the Teradata Forums. Read the guidelines on posting. Email notifications and RSS feeds are available, and also a chronological list of all forum activity.
 Print All Forums Analytics 17 Apr 2009 Difference between Timestamp Hi,Can some one help me how to calculate the Difference of Timestamp fields/columns?Thanks in AdvanceGopi 17 Apr 2009 Gopi,It should simply be the arithmetic difference, ie timestamp2 - timestamp1.What problem are you having?The following is taken straight out of the Teradata Functions and Operators reference manual.Example 1: Subtract two TIMESTAMP TypesConsider the following table: CREATE TABLE BillDateTime (start_time TIMESTAMP(0) ,end_time TIMESTAMP(0));Determine the difference, specifying an Interval unit of DAY TO SECOND for the result: SELECT (end_time - start_time) DAY(4) TO SECOND FROM BillDateTime;The DAY(4) specifies four digits of precision, and allows for a maximum of 9999 days, orapproximately 27 years.The result is an Interval that looks like this: 5 16:49:20.340000(Note the format specification for the result.)Peter 18 Apr 2009 Thanks for the reply,I tried like field1 - field2 without DAY(n) command and it throwed the error. I got it now. 11 Mar 2013   What can you do if there are more than 9999 days between the timestamps?  I am trying to get the number of seconds elapsed (ideally as decimal rather than interval).  For instance, this gives an error:   `SELECT` `CURRENT_TIMESTAMP AS t2,` `(t2 - INTERVAL '9999' DAY - INTERVAL '30' DAY) t1,` `(t2 - t1) DAY(4) TO SECOND delta`   I can write a case statement that returns results only when the difference is between +/-9999 days, but when the difference is outside this range, it can only return `null`.  I would like to get the number of seconds for these cases.   12 Mar 2013 From another thread and some playing around, I found that this works:   ```SELECT CURRENT_TIMESTAMP AS t2, (t2 - INTERVAL '9999' DAY - INTERVAL '30' DAY + INTERVAL '1.3' SECOND) AS t1, CAST((CAST(t2 AS DATE) - DATE '1970-01-01') * 86400 + (EXTRACT(HOUR FROM t2) * 3600) + (EXTRACT(MINUTE FROM t2) * 60) AS DECIMAL(18)) + (EXTRACT(SECOND FROM t2)) AS t2_s, CAST((CAST(t1 AS DATE) - DATE '1970-01-01') * 86400 + (EXTRACT(HOUR FROM t1) * 3600) + (EXTRACT(MINUTE FROM t1) * 60) AS DECIMAL(18)) + (EXTRACT(SECOND FROM t1)) AS t1_s, t2_s - t1_s AS delta_s``` 02 Apr 2013 hi, i need to know more about the below code seconds := (3600 * (To_Number(extract(HOUR FROM diff)))) + (60 * (To_Number(extract (MINUTE FROM diff)))) +(To_Number(extract(SECOND FROM diff))); I guess TO_NUMBER() does not work in teradata So how this part of code can be convert into Teradata. Highly appriciate your quick response.   02 Apr 2013 Conversion is easy, just remove the TO_NUMBER :-) Or use TD14, which supports it. There was a thread last week on timestamp difference in seconds/minutes: http://forums.teradata.com/forum/analytics/failure-7453-interval-field-overflow#comment-27048 Dieter Dieter You must sign in to leave a comment.