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

Senior Supporter

## Re: Issue while comparing time with flaot

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