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 to the Top
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
Options
# 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

6 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

Dieter

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.