Home Page for the TeradataForum
 

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016