Issue while comparing time with flaot

General
Enthusiast

Issue while comparing time with flaot

My requiement is to compare stepstarttime with thetime from dbc.resuagespma .

When I try to add 10 minutes to thetime Iam getting Invaid datetime ,interval error .

Please suggest what is the best way to add 10 minutes to thetime

 

SELECT THEDATE, THETIME,QUERYID,SUM(CPUTIME)
 FROM DBC.DBCSTEP  A , DBC.RESUSAGESPMA B
 WHERE LOGDATE = THEDATE
 AND CAST(SUBSTR(CAST(STEPSTARTTIME AS VARCHAR(19)),11,9) AS TIME(0))  BETWEEN THETIME  AND
  CAST(THETIME +10   AS FLOAT FORMAT  '99:99:99' )
GROUP BY 1,2,3

1 REPLY
Senior Supporter

Re: Issue while comparing time with flaot

Hi radsubra,

 

Assuming that your resusage data (SPMA) in logged at 10 minute intervals you can use the following.

 

Bearing in mind that resusage times are at the end of the time interval (so a value of 10:20:00 represents what happened between 10:10:01 and 10:20:00), the way that I do this is to adjust the timestamp value (stepstarttime in your example) to next highest 10 minute interval. This should work for you:

SELECT THEDATE, THETIME,QUERYID,SUM(CPUTIME)
 FROM DBC.DBCSTEP A , DBC.RESUSAGESPMA B
 WHERE LOGDATE = THEDATE
  AND CAST( (b.thetime (FORMAT '99:99:99')) AS CHAR(8))
       =  SUBSTRING( CAST((CASE
	          WHEN EXTRACT(MINUTE FROM stepstarttime) = 0 AND EXTRACT(SECOND FROM stepstarttime) = 0.00 THEN stepstarttime
	          ELSE CAST( (SUBSTRING(CAST(stepstarttime AS CHAR(22)) FROM 1 FOR 15)||'0:00') AS TIMESTAMP(0)) + INTERVAL '10' MINUTE
	          END) AS CHAR(26)) FROM 12 FOR 8)
GROUP BY 1,2,3;

Give that a go and see what happens.

Cheers, Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com