Search code examples
teradatasqldatetime

Select records having a time interval greater than a certain value in Teradata

I am stuck with a simple problem related to finding out which queries took more than usual to complete. My script is the following:

locking row for access
SELECT 
username, 
CollectTimeStamp, 
((firstresptime - starttime  ) HOUR TO second  ) AS ElapsedTime,
((firstresptime - firststeptime  ) HOUR  TO second ) AS ExecutionTime,
CAST(((firstresptime - firststeptime) SECOND) AS INTEGER) AS ExecutionTimeInt,
(ElapsedTime - ExecutionTime) AS Delay, 
-- other kpis here
FROM dbql_data.dbql_all
where username ='MyUser'; 
and dateofday> '2017-07-01'
    and ExecutionTimeInt > 5

However, I get records having ExecutionTimeInt less than 5.

Question: how can I get the records having a timeinterval greater than a certain value?

Extra info:

select * from dbc.dbcinfo; returns 

    InfoKey InfoData
1   VERSION 15.10.04.10
2   RELEASE 15.10.04.02
3   LANGUAGE SUPPORT MODE   Standard
Solution

  • The ExecutionTimeInt calculation is likely to fail with an Interval overflow as it's limited to 9999 seconds.

    ElapsedTime is an Interval, the correct way to compare is:

    WHERE ElapsedTime > interval '5' second

    or 

    WHERE ElapsedTime > interval '1' minute