Analytics

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- 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

3 weeks ago

3 weeks ago

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

3 weeks ago

3 weeks ago

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.

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.