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
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.