Archives of the TeradataForum

Message Posted: Wed, 26 Mar 2003 @ 17:33:34 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Timestamp difference

From:		Geoffrey Rommel

For example tblXYZ is the table, BEGIN_TIME is the smaller timestamp and END_TIME is the larger timestamp, the following gave me inaccurate results.

SELECT minute(END_TIME - BEGIN_TIME) FROM tblXYZ;

I want the total difference in minutes sometimes and total difference in seconds sometimes.

The issue is even if I do that case, the answer is not right.

If I understand your requirements, you want to compute the difference in seconds (or minutes) between two timestamps. Try this...

First, subtract them. The difference will be an INTERVAL, not a number.

select ts01 - ts04 day(4) to second
 from dttest;

(ts01 - ts04) DAY TO SECOND
---------------------------
          2 07:11:24.000000

Now, you need to extract the day portion. This will be an integer.

select extract(day from (ts01 - ts04 day(4) to second))
 from dttest;

EXTRACT(DAY FROM (ts01 - ts04) DAY TO SECOND)
---------------------------------------------
                                            2

That integer times 86400 gives you the number of seconds.

select extract(day from (ts01 - ts04 day(4) to second)) * 86400
 from dttest;

(EXTRACT(DAY FROM (ts01 - ts04) DAY TO SECOND)*86400)
-----------------------------------------------------
                                               172800

Now do likewise for hours, minutes, and seconds, and you have the total difference in seconds.

select (extract(day from (ts01 - ts04 day(4) to second)) * 86400)
  + (extract(hour from (ts01 - ts04 day(4) to second)) * 3600)
  + (extract(minute from (ts01 - ts04 day(4) to second)) * 60)
  + extract(second from (ts01 - ts04 day(4) to second))
 from dttest;

((((EXTRACT(DAY FROM (ts01 - ts04) DAY TO SECOND)*86400)+(EX
------------------------------------------------------------
                                               198684.000000

The same approach, mutatis mutandis, will give you the difference in minutes.


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference