Total Time duration

[sundar sankaran]

Hi ALL,
I need to get total time duration of my user in my application. I got maximum time duration he has spent for a month from the below sample query .

SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss");
Date parseFinalTime=null;
String totalDuration = "";
long totDuration=0;
String siteDuration="";
String masterquery = new StringBuffer("SELECT max(TSIE52_SESSION_DURATION)  from STSIE52_USER_SESSION ").toString();
ResultSet subResultSet = getResultSet(connection, masterquery);
while (subResultSet.next()) {
parseFinalTime = sdf.parse(subResultSet.getString(1));
totDuration+ =parseFinalTime.getTime();
}
 

totalDuration = sdf.format(new Date(totDuration));
System.out.println("siteDuration---------------->::" +totalDuration);

The output I got as below:
siteDuration---------------->::00:00:24
siteDuration---------------->::00:00:05
siteDuration---------------->::00:00:09
siteDuration---------------->::00:00:14
siteDuration---------------->::00:51:33
siteDuration---------------->::00:16:42
siteDuration---------------->::00:00:05
siteDuration---------------->::00:00:25
siteDuration---------------->::00:00:05
siteDuration---------------->::00:00:10
siteDuration---------------->::00:00:09
siteDuration---------------->::00:03:02
siteDuration---------------->::00:00:18
siteDuration---------------->::00:00:11

I need to total all the times mentioned above and get the result as total duration time. How it is possible in java?
I have tried with Sum function in SQl, but it will not total the time(HH:MM:SS), it can total only the integer values. The resultset i am getting is set of all the max time duration the user has spent for a month, so i need to total all the values and get the output as single Time in HH:MM:SS format.
Help in java code is appreciated.

[Stefan Wagner]

Simplifying your statement (removing needless intermediate StringBuffer):

String masterquery = "SELECT max(TSIE52_SESSION_DURATION) from STSIE52_USER_SESSION ";

I'm asking, how this query might return multiple rows?
You don't have a GROUP clause, so SELECT MAX (x) FROM (y) should return just one value - the maximum.

Probably you're hiding something here?

and you tried

"SELECT sum(max(TSIE52_SESSION_DURATION)) from STSIE52_USER_SESSION group by SOMETHING";

without success?

[sundar sankaran]

I thought the fully query is not important. The resultset i am getting is set of all the max time duration the user has spent for a month, so i need to total all the values and get the output as single Time in HH:MM:SS format. Hope this clarifies your doubts. This is my query:

String masterquery =new StringBuffer("SELECT MAX(TSIE52_SESSION_DURATION)  from STSIE52_USER_SESSION ")
.append("  where TSIE52_LOGIN_DATE BETWEEN TRUNC(TO_DATE('"+fromDate+"','MM/DD/YYYY'))  ")
.append("  AND  TRUNC(TO_DATE('"+toDate+"','MM/DD/YYYY')) and TSIE52_USERID_C='" + userID + "' ")
.append(" and TSIE52_COUNTRY_C='" + countryCode + "'and TSIE52_SITE_C='" + siteCode + "' group by TSIE52_SESSION_TIME_IN").toString();