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 GopiKrishnan S 18 posts Joined 02/08 17 Apr 2009 Difference between Timestamp Hi,Can some one help me how to calculate the Difference of Timestamp fields/columns?Thanks in AdvanceGopi pj.routledge 7 posts Joined 04/08 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 GopiKrishnan S 18 posts Joined 02/08 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. dlabar 5 posts Joined 06/11 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.   dlabar 5 posts Joined 06/11 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``` nt 1 post Joined 03/13 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.   dnoeth 4628 posts Joined 11/04 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. Active Posters