Analytics

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Highlighted
##
##### Difference between Timestamp

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-17-2009
06:12 AM

04-17-2009
06:12 AM

Hi,

Can some one help me how to calculate the Difference of Timestamp fields/columns?

Thanks in Advance

Gopi

Can some one help me how to calculate the Difference of Timestamp fields/columns?

Thanks in Advance

Gopi

8 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-17-2009
04:38 PM

04-17-2009
04:38 PM

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 Types

Consider 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, or

approximately 27 years.

The result is an Interval that looks like this:

5 16:49:20.340000

(Note the format specification for the result.)

Peter

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 Types

Consider 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, or

approximately 27 years.

The result is an Interval that looks like this:

5 16:49:20.340000

(Note the format specification for the result.)

Peter

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-18-2009
10:18 AM

04-18-2009
10:18 AM

Thanks for the reply,

I tried like field1 - field2 without DAY(n) command and it throwed the error. I got it now.

I tried like field1 - field2 without DAY(n) command and it throwed the error. I got it now.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-11-2013
07:00 AM

03-11-2013
07:00 AM

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.- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-12-2013
08:00 AM

03-12-2013
08:00 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-02-2013
12:12 AM

04-02-2013
12:12 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-02-2013
10:19 AM

04-02-2013
10:19 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-05-2017
03:53 PM

06-05-2017
03:53 PM

Hi,

Did you get any solution to find the timestamp difference in seconds.

Im looking for some solution for the below.

GopiKrishnan_S wrote:

Hi,

Can some one help me how to calculate the Difference of Timestamp fields/columns?

Thanks in Advance

Gopi

ex: t1 = 4/15/2017 17:34:52

t2 = 4/16/2017 18:43:54

t2 - t1 = 86400+3600+540+ 2 =3686942 seconds

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-05-2017
04:00 PM

06-05-2017
04:00 PM

Can anyone provide me solution for timestamp difference in seconds.

t1 = 4/15/2017 17:34:52

t2 = 4/16/2017 18:43:54

t2 - t1 = 86400+3600+540+ 2 =90542 seconds

i know that if we subtract timestamps we will get

('4/17/2017 10:43:17'- '4/15/2017 17:34:52') DAY(4) TO SECOND as diff the answer is 1 17:08:25.000000. But im looking for some solution that give me caluculated value in Seconds.

Thanks in advance.