SELECT failed 7453 : interval field overflow

Database
Enthusiast

SELECT failed 7453 : interval field overflow

I want to get querycount which ran less than 30 secs, For that I wrote this query but its giving SELECT failed 7453 : interval field overflow. For that I tried to fetch record for five days only 

WHERE CollectTimeStamp between '2014-06-01 00:00:00' and '2014-06-05 23:59:59' 

and its worked(count(1)=172,963) . But for next five days 

WHERE CollectTimeStamp between '2014-06-05 00:00:00' and '2014-06-10 23:59:59' 

it again threw same error now I fetched for one day 

WHERE CollectTimeStamp between '2014-06-05 00:00:00' and '2014-06-06 23:59:59' 

it worked again(answerset count(1)=65407). 

My question is Why its behaving like this? How to overcome this error. cz here as i can see only changing time intervals in condition WHERE CollectTimeStamp between '2014-06-05 00:00:00' and '2014-06-06 23:59:59' effecting result. How to successfully run it for whole month i.e. 30 days

For reference:

Query used is

-------------------------

LOCKING ROW FOR ACCESS

SELECT count(1)

FROM DBC.DBQLOGTBL

WHERE CollectTimeStamp between '2014-06-01 00:00:00' and '2014-06-30 23:59:59' 

AND USERNAME='Proactive_USER'

and statementtype='SELECT'

and ((FIRSTRESPTIME - FIRSTSTEPTIME) second (4)) <= 30;

---------------------------------

And datatypes format for columns used:

CREATE MULTISET TABLE dbc.DBQLOGTBL ,FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

CollectTimeStamp TIMESTAMP(2) FORMAT 'YYYY-MM-DDBHH:MI:SS' NOT NULL,

StartTime TIMESTAMP(2) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(F)Z' NOT NULL,

 FirstStepTime TIMESTAMP(2) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(F)Z' NOT NULL,

 FirstRespTime TIMESTAMP(2) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(F)Z',

StatementType CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

UserName VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC);

PRIMARY INDEX ( ProcID ,CollectTimeStamp );

@moderator plz move it to correct forum if u feel it not appropriate for database forum.

Thanks in advance.

P.s. Any other way to get querycount from dbc also welcomed as i just did it in beginner level. :)

3 REPLIES
Junior Contributor

Re: SELECT failed 7453 : interval field overflow

There were queries running longer than 9999 seconds = 02h 46m 39s, so simply increase the interval. 

When you check the source for dbc.qryLogV you will notice a column named ElapsedTime which is calculated as ((firstresptime - starttime) hour(4) to second).

And then your condition will be

WHERE ElapsedTime <= INTERVAL '30' SECONDS
Enthusiast

Re: SELECT failed 7453 : interval field overflow

Hey,

Thank u so much.Really helpful!!

but what if i write this

------------------------

 LOCKING ROW FOR ACCESS

SELECT count(1)

FROM DBC.DBQLOGTBL

WHERE CollectTimeStamp between '2014-06-01 00:00:00' and '2014-06-30 23:59:59' 

AND USERNAME='Proactive_USER'

and statementtype='SELECT'

and ((FIRSTRESPTIME - FIRSTSTEPTIME) hour(4) to second ) <= 30;

-------------------

(as i want execution time)

 but its not running saying select failed 5407: invalid operation date time or interval.

or i should add it in view another column ((FIRSTRESPTIME - FIRSTSTEPTIME) hour(4) to second ) (Named ExecutionTime ) ??

Junior Contributor

Re: SELECT failed 7453 : interval field overflow

You can't compare an HOUR TO SECOND interval to an INTEGER. What is 30 supposed to be. hours, minutes or seconds?

((FIRSTRESPTIME - FIRSTSTEPTIME) hour(4) to second ) <= INTERVAL '30' SECOND;