I have a field, TOTALTIME, with a format of HH:MM:SS. The datatype is TIME (HHIS). How can I calculate a subtotal?

Example: I have three different cumulative times and I need to add them up. 46 hrs & 39 mins(46:39:00) + 47 mins (00:47:00) + 1 hr & 35 mins (01:35:00) = 49 hrs & 1 min (49:01:00)

Thanks,
Denise

This message has been edited. Last edited by: DMA,

WebFOCUS does not total Date, Date-time or Time fields. I would convert the time fields to milliseconds, add them up and then maybe convert them back to hours, minutes and seconds.

Francis,

Can you please give me an example of that or direct me to where I can find an example?

Thanks!

Take alook at the DateTime functions documentation.

DateTime Functions

Function HTIME will convert your time field to milli- or micro-seconds. Then you should be able to sum them and calculate total hours, minutes, and/or seconds.

Dan,

I was able to convert my time field to milliseconds and it sums, however, I'm not sure how to calculate and display it in HH:MM:SS format.

Denise

A series of computations involving division and remainders will figure out how many hours, minutes and seconds there are in n milliseconds.

A minute has 60 seconds
an hour 60 minutes
a Day 24 hours

so if you have 100751 seconds

Days: 100751/(60*60*24) gives 1.1661, rounded 1 day
the formula should be
DAY/I3=100751/(60*60*24); will give 1
REST1/I8=100751-DAY*(60*60*24); gives 14351
HOURS/I3=REST1/60*60); gives 3.98 hours --> 3
REST2/I8=REST1-HOURS*60*60; gives 3551
MINUTES/I3=REST2/60; 59.2 --> 59 minutes
SECONDS/I3=REST2-MINUTES*60; 11 seconds

combine this and you get 1day 3:59:11

I hope this helps a bit

I hope my math is correct:

DEFINE FILE CAR
T1_MS/D8 = IF SEATS EQ 2 THEN 18270347 ELSE IF SEATS EQ 4 THEN 38237731 ELSE 2993787;
END
TABLE FILE CAR
SUM 
T1_MS

COMPUTE T1_HH/I4  = T1_MS / 1000 / 60 / 60;
COMPUTE T1_R1/I10 = IMOD(T1_MS, 1000*60*60, 'I10'); NOPRINT
COMPUTE T1_MM/I2  = T1_R1 / 1000 / 60;
COMPUTE T1_R2/I10 = IMOD(T1_R1, 1000*60, 'I10'); NOPRINT
COMPUTE T1_SS/I2  = T1_R2 / 1000;

COMPUTE T1_TIME/A10 = PTOA(T1_HH, '(P4)', 'A4') || ':' | PTOA(T1_MM, '(P2)', 'A2') || ':' || PTOA(T1_SS, '(P2)', 'A2');
BY COUNTRY
END

Francis,

That worked just fine, however, I need to get a grand total of the T1_TIME COMPUTED field.

Example: In the end I need to display 85:08:08 for that field.

Denise

A multi-verb request might work (stealing from Francis' nice example):

DEFINE FILE CAR
 T1_MS/D8 = IF (SEATS EQ 2) THEN 18270347 ELSE
            IF (SEATS EQ 4) THEN 38237731 ELSE 2993787 ;
END
-*
TABLE FILE CAR
 SUM
  COMPUTE T1_MST/D10 = T1_MS ; NOPRINT
  COMPUTE T1_HHT/P4  = T1_MST / 1000 / 60 / 60; NOPRINT
  COMPUTE T1_R1T/I10 = IMOD(T1_MST, 1000*60*60, 'I10'); NOPRINT
  COMPUTE T1_MMT/P2  = T1_R1T / 1000 / 60; NOPRINT
  COMPUTE T1_R2T/I10 = IMOD(T1_R1T, 1000*60, 'I10'); NOPRINT
  COMPUTE T1_SST/P2  = T1_R2T / 1000; NOPRINT
  COMPUTE T1_TIMET/A10 = PTOA(T1_HHT, '(P4)' , 'A4') || ':' ||
                         PTOA(T1_MMT, '(P2L)', 'A2') || ':' ||
					     PTOA(T1_SST, '(P2L)', 'A2'); NOPRINT
-*
 SUM
  T1_MS
  COMPUTE T1_HH/P4  = T1_MS / 1000 / 60 / 60;
  COMPUTE T1_R1/I10 = IMOD(T1_MS, 1000*60*60, 'I10'); NOPRINT
  COMPUTE T1_MM/P2  = T1_R1 / 1000 / 60;
  COMPUTE T1_R2/I10 = IMOD(T1_R1, 1000*60, 'I10'); NOPRINT
  COMPUTE T1_SS/P2  = T1_R2 / 1000;
  COMPUTE T1_TIME/A10 = PTOA(T1_HH, '(P4)' , 'A4') || ':' ||
                        PTOA(T1_MM, '(P2L)', 'A2') || ':' ||
						PTOA(T1_SS, '(P2L)', 'A2');
 BY COUNTRY
 ON TABLE SUBFOOT
  "TOTALS<T1_MST<T1_HHT<T1_MMT<T1_SST<T1_TIMET"
 ON TABLE SET STYLE *
  TYPE=REPORT, JUSTIFY=CENTER, $
  TYPE=TITLE, BACKCOLOR=LIGHT BLUE, $
  TYPE=TABFOOTING, HEADALIGN=BODY, STYLE=BOLD, $
 ENDSTYLE
END

This message has been edited. Last edited by: Dan Satchell,

Without resorting to too much mathematics:

DEFINE FILE Fn 
-* Separate hours, minutes and seconds from incoming time field in HHIS format
hrs/I4 = HPART(INPUTTIME,'hh','I4');
min/I2 = HPART(INPUTTIME,'mi','I2');
sec/I2 = HPART(INPUTTIME,'ss','I2');
-* Set hour part to zero. Hour part limited to 23 in H field, so calc separately. First record value will be 00:00:00
zTime/HHIS = HSETPT(zTime,'hh',0,8,'HHIS');
-* Add seconds and minutes from incoming time field to zTime
pmin/HHIS = HADD(HADD(zTime,'ss',sec,8,'HHIS'),'mi',min,8,'HHIS');
-* Add hours together. Previous hours plus current hours plus hours from adding minutes and seconds.
phrs/I4 = LAST phrs + hrs + HPART(pmin,'hh','I4');
-* Set time to new time for next record
zTime = pmin;
-* Pull time string together
timeString/A12 = FTOA(phrs,'(F4)','A4') || ':' || EDIT(HPART(pmin,'mi','I2')) || ':' || EDIT(HPART(pmin,'ss','I2')); 
END
TABLE FILE Fn
PRINT INPUTTIME 
      timeString 
      zTime NOPRINT 
END

gives:
INPUTTIME timeString 
11:12:12  11:12:12 
09:43:53  20:56:05 
11:36:41  32:32:46 
08:32:59  41:05:45 

TABLE FILE Fn
SUM INPUTTIME 
      timeString 
      zTime NOPRINT 
END

gives:
INPUTTIME timeString 
08:32:59  41:05:45 

(zTime NOPRINT is required for full DEFINE evaluation...)

I've coded the following using some of the above examples and I've gotten the desired outcome. However, it appears that the length of the integer portion is too short. I am getting asterisks. How can I make it larger? I've tried changing '(P4)' to '(P5 or higher)' but it only serves to shorten the integer length further.


EXAMPLE:
TABLE FILE PCR_USAGE_TEST
SUM
NUMBERPATRONS
COMPUTE THH/I4 = TOTALMINUTES / 60; NOPRINT
COMPUTE TMM/I2 = IMOD(TOTALMINUTES, 60, 'I10'); NOPRINT
COMPUTE TSS/I2 = IMOD(TMM, 1000*60, 'I10')/ 1000; NOPRINT
COMPUTE TOT_MINS/A10 = PTOA(THH, '(P4)', 'A4') || ':' ||
PTOA(TMM, '(P2L)', 'A2') || ':' ||
PTOA(TSS, '(P2L)', 'A2'); AS 'Total Time'

WHERE REPORTDATE FROM DT('2011-02-01') TO DT('2011-02-03');
END

UNDESIRED RESULT:
Total Time
****:56:00

When you made the P4 larger in the compute statement for hours did you also make THH/I4 bigger - ie I6? And did you try to make your output bigger - i.e. TOT_MINS/A20?

quote:

Originally posted by N.Selph:
When you made the P4 larger in the compute statement for hours did you also make THH/I4 bigger - ie I6? And did you try to make your output bigger - i.e. TOT_MINS/A20?

N.Selph,

I did make THH and TOT_MINS bigger. When I change it to I6 I get 222, when I change it to 5 I get 2225. The total number of hours is 22,250. It cuts off the 0.

I was able to figure out what was wrong. Thanks to everyone for your input!