Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [WORKAROUND] NFR: TIMEDIFF

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[WORKAROUND] NFR: TIMEDIFF
 Login/Join
 
Expert
posted
Problem: It is very cumbersome to perform calculations on time fields (hh:mm:ss) in DEFINE or TABLE file requests and have the results displayed in the ON FIELD or ON TABLE SUBTOTAL or (aligned) SUBFOOT lines. Treating these results as numbers would be nice.
Action: Create a function which calculates time differences directly from a field which is formatted as 'HYYMDS'.
Solution: Reports will show the column total in the ON TABLE or ON FIELD SUBTOTAL or (aligned) SUBFOOT lines.

All thoughts are welcome. Thanks, Doug

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




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Master
posted Hide Post
I totally agree, which is why we created a SQL Proc and call it from WebFOCUS. Makes life a lot easier.



- FOCUS Man, just FOCUS!
-----------------------------
Product: WebFOCUS
Version: 8.1.04
Server: Windows 2008 Server
 
Posts: 578 | Registered: October 01, 2014Report This Post
Expert
posted Hide Post
So, I can't live up to my mantra: "Yes! We Can Do that In WebFOCUS" ... Until we get this.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Master
posted Hide Post
Can't you create your own custom define field to do the calculation and then convert it to a numeric?


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Expert
posted Hide Post
I can do the calculations, row by row. But not the totaling, displayed in hh:mm:ss format.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Expert
posted Hide Post
GaninL,
quote:
a SQL Proc and call it from WebFOCUS
Can you share it?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Expert
posted Hide Post
Doug,

Did you miss this thread?


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
Thanks Tony, I can get the time differences but not the totals. Consider this code:
DEFINE FUNCTION HTIMEDIF(ENDTIME/HYYMDs, BEGTIME/HYYMDs)
  DT_NULL/HYYMDs = HINPUT(14, '00010101000000000', 8, 'HHIS');
  HTIMEDIF/HYYMDs = HADD(DT_NULL, 'MILLISECONDS',HDIFF(ENDTIME, BEGTIME, 'MILLISECONDS', 'D12.2'), 8, 'HYYMDs');
END

-* Display Elapsed time with zero padding removed
DEFINE FUNCTION HTIMEDFA(ENDTIME/HYYMDs, BEGTIME/HYYMDs)
  DT_NULL/HYYMDs = HINPUT(14, '00010101000000000', 8, 'HYYMDs');
  HTIMEDIF/HYYMDs = HADD(DT_NULL, 'MILLISECONDS',HDIFF(ENDTIME, BEGTIME, 'MILLISECONDS', 'D12.2'), 8, 'HYYMDs');
  HRI /I2 =HPART(HTIMEDIF,'hh','I2');
  MNI /I2 =HPART(HTIMEDIF,'mi','I2');
  SCI /I2 =HPART(HTIMEDIF,'ss','I2');
  MSI /I3 =HPART(HTIMEDIF,'ms','I3');
  HRA /A3 =IF HRI             EQ 0 THEN '' ELSE FPRINT(HRI,'I2','A2')||':';
  MNA /A3 =IF HRI+MNI         EQ 0 THEN '' ELSE FPRINT(MNI,'I2','A2')||':';
  SCA /A3 =IF HRI+MNI+SCI     EQ 0 THEN '' ELSE FPRINT(SCI,'I2','A2');
  MSA /A4 =IF HRI+MNI+SCI+MSI EQ 0 THEN '0' ELSE '.'||EDIT(MSI)            ;
  HTIMEDFA/A12=STRIP(13,HRA||MNA||SCA||MSA,' ','A12');
END

DEFINE FILE CAR
  BEG_TIME/HYYMDs = HINPUT(23, '2014-12-01 06:25:23.345', 8, 'HYYMDs');
  END_TIME/HYYMDs = HINPUT(23, '2014-12-01 11:55:43.345', 8, 'HYYMDs');
END

TABLE FILE CAR
  SUM BEG_TIME
      END_TIME
  COMPUTE C_DIFF1/A12 = FPRINT(HTIMEDIF(END_TIME, BEG_TIME),'HHISs','A12'); AS 'Elapsed'
  COMPUTE C_DIFF2/A12 = HTIMEDFA(END_TIME, BEG_TIME); AS 'Elapsed'

BY COUNTRY AS ''
-*WHERE COUNTRY EQ 'ENGLAND'
ON TABLE SET PAGE NOLEAD
ON TABLE SUBTOTAL
ON TABLE SET STYLE *
TYPE=DATA,JUSTIFY=RIGHT,$
ENDSTYLE
END
which gives these results
          BEG_TIME                END_TIME                Elapsed 
ENGLAND   2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 5:30:20.000 
FRANCE    2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 5:30:20.000 
ITALY     2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 5:30:20.000 
JAPAN     2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 5:30:20.000 
W GERMANY 2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 5:30:20.000 
TOTAL                                                     (NO TOTALS)


What we need is to see the totals in the hh:mm:ss format (ref: my post on this page posted Tue Jun 16 2015 16:59 . We don't care about the milliseconds. As a matter of fact, the input that we are using is in hh:mm:ss format. So, no need for the rest. However, "the rest" would be good as well.

Thanks, Doug

This message has been edited. Last edited by: Doug,
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Virtuoso
posted Hide Post
If IBI is considering implementing a NF, they should give careful thought to how the "time-difference" result is formatted. Which is probably a whole nother topic.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Expert
posted Hide Post
Actually Jack, That,"how the "time-difference" result is formatted", is the main point that I'm asking for. The following is my requested result:
ENGLAND   2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 05:30:20 
FRANCE    2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 05:30:20 
ITALY     2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 05:30:20 
JAPAN     2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 05:30:20 
W GERMANY 2014/12/01 06:25:23.345 2014/12/01 11:55:43.345 05:30:20 
TOTAL                                                     27:31:00
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Master
posted Hide Post
quote:
Can you share it?


Unfortunately, I can't.. However, here is something I found online that might help.

WITH ex_table AS (
	SELECT '2007-01-01' 'birthdatetime', '2009-03-29' 'visitdatetime')
SELECT CAST(DATEDIFF(yy, t.birthdatetime, t.visitdatetime) AS VARCHAR(4)) +' year '+
       CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime) AS VARCHAR(2)) +' month '+
       CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime), DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime)), t.visitdatetime) AS VARCHAR(2)) +' day' AS result
FROM ex_table t


You'll have to format it the way you like it, but it gives a good example of it's use.

Results:
2 year 2 month 28 day



- FOCUS Man, just FOCUS!
-----------------------------
Product: WebFOCUS
Version: 8.1.04
Server: Windows 2008 Server
 
Posts: 578 | Registered: October 01, 2014Report This Post
Virtuoso
posted Hide Post
Doug,
Your "Elapsed"is an Alpha so, definitely there is no totals. You are asking for a new data type which could be totaled...
In the meantime, how about a RECAP and a SUBFOOT?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Expert
posted Hide Post
Yep Danny, a new data type which could be totaled would be as succinct as it gets. At least for a time only field. But, Better yet, as long as they're at it the NFR should include a fill date/time field (HYYMDs). But, at least the time portion. The HYYMDs would allow the similar function of the DATEADD function with the addition of time.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Expert
posted Hide Post
I smell a new case for this NFR coming soon...
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Expert
posted Hide Post
In the meantime I'll create a function for this:
DEFINE FUNCTION TIMEDIFF (BEGTIME/A8 ,ENDTIME/A8)
. . .
END
I'll use this as the basis of the NFR to standardize it.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Expert
posted Hide Post
Hi Doug,

I don't think that a function will help you at all because, as Danny states, you're trying to sub-total an alpha field.

Even an NFR might not help because you need to have an internal format that can be displayed in HHISs with the hours portion containing all hours held within the date-time field since the base date. Summating this might be an issue because I can foresee that the date-time would need to be held as a count of milliseconds since the base date-time but output as HHISs as above. Frowner

You can achieve what you want to do in two passes of the data, using FML - obviously you would have to pre-parse your main sort field(s) to allow them to be used in DM to force the FOR syntax plus the final ROWS ... OVER syntax, but that shouldn't be a big deal for someone like you. I have only used a simplified DAY portion in this instance but would suggest getting the day difference between the RECAP date-time and the base date-time before multiplying by 24 for hours.

DEFINE FUNCTION HTIMEDIF(ENDTIME/HYYMDs, BEGTIME/HYYMDs)
  DT_NULL/HYYMDs  = HINPUT(14, '00010101000000000', 8, 'HHIS');
  HTIMEDIF/HYYMDs = HADD(DT_NULL, 'MILLISECONDS',HDIFF(ENDTIME, BEGTIME, 'MILLISECONDS', 'D12.2'), 8, 'HYYMDs');
END

DEFINE FILE CAR
  BEG_TIME/HYYMDs = HINPUT(23, '2014-12-01 06:25:23.345', 8, 'HYYMDs');
  END_TIME/HYYMDs = HINPUT(23, '2014-12-01 11:55:43.345', 8, 'HYYMDs');
END

TABLE FILE CAR
  SUM BEG_TIME
      END_TIME
	  COMPUTE C_DIFF/D12      = HTIME(8, HTIMEDIF(END_TIME, BEG_TIME), 'D12');
	  COMPUTE DT_BASE/HYYMDs  = HINPUT(14, '00010101000000000', 8, 'HYYMDs');
	  COMPUTE HTIMEDIF/HYYMDs = HADD(DT_BASE, 'MILLISECONDS',C_DIFF, 8, 'HYYMDs');
      COMPUTE DDI /I2 = HPART(HTIMEDIF,'dd','I2')-1;
      COMPUTE HRI /I2 = HPART(HTIMEDIF,'hh','I2');
      COMPUTE MNI /I2 = HPART(HTIMEDIF,'mi','I2');
      COMPUTE SCI /I2 = HPART(HTIMEDIF,'ss','I2');
      COMPUTE MSI /I3 = HPART(HTIMEDIF,'ms','I3');
FOR COUNTRY
'ENGLAND'   LABEL L1 OVER
'FRANCE'    LABEL L2 OVER
'ITALY'     LABEL L3 OVER
'JAPAN'     LABEL L4 OVER
'W GERMANY' LABEL L5 OVER
'TOTALS'    LABEL L6 OVER
RECAP L6(3)  = L1(3) + L2(3) + L3(3) + L4(3) + L5(3); OVER
RECAP L6(4)  = L5(4); OVER
RECAP L6(5)  = HADD(L6(4), 'MILLISECONDS',L6(3), 8, 'HYYMDs'); OVER
RECAP L6(6)  = HPART(L6(5),'dd','I2') - 1; OVER
RECAP L6(7)  = HPART(L6(5),'hh','I2') + (L6(6) * 24); OVER
RECAP L6(8)  = HPART(L6(5),'mi','I2'); OVER
RECAP L6(9)  = HPART(L6(5),'ss','I2'); OVER
RECAP L6(10) = HPART(L6(5),'ms','I2');
ON TABLE HOLD AS TEMPHLD1
END
-RUN

DEFINE FILE TEMPHLD1
  HRA /A3      = IF HRI             EQ 0 THEN '' ELSE FPRINT(HRI,'I2','A2')||':';
  MNA /A3      = IF HRI+MNI         EQ 0 THEN '' ELSE FPRINT(MNI,'I2','A2')||':';
  SCA /A3      = IF HRI+MNI+SCI     EQ 0 THEN '' ELSE FPRINT(SCI,'I2','A2');
  MSA /A4      = IF HRI+MNI+SCI+MSI EQ 0 THEN '0' ELSE '.'||EDIT(MSI)            ;
  HTIMEDFA/A12 = STRIP(13,HRA||MNA||SCA||MSA,' ','A12');
END

TABLE FILE TEMPHLD1
PRINT BEG_TIME
      END_TIME
	  HTIMEDFA AS 'Elapsed'
   BY E01 AS COUNTRY ROWS 'ENGLAND' OVER 'FRANCE' OVER 'ITALY' OVER 'JAPAN' OVER 'W GERMANY' OVER 'TOTALS'
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
TYPE=DATA,JUSTIFY=RIGHT,$
ENDSTYLE
END
-RUN


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
BTW, this gives the following output -

COUNTRY   BEG_TIME                END_TIME                 Elapsed 
  ENGLAND 2014/12/01 06:25:23.345 2014/12/01 11:55:43.345  5:30:20.000 
   FRANCE 2014/12/01 06:25:23.345 2014/12/01 11:55:43.345  5:30:20.000 
    ITALY 2014/12/01 06:25:23.345 2014/12/01 11:55:43.345  5:30:20.000 
    JAPAN 2014/12/01 06:25:23.345 2014/12/01 11:55:43.345  5:30:20.000 
W GERMANY 2014/12/01 06:25:23.345 2014/12/01 11:55:43.345  5:30:20.000 
   TOTALS                                                 27:31:40.000


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
I'll create a DEFINE FUNCTION for this while waiting for the NFR to be fulfilled.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [WORKAROUND] NFR: TIMEDIFF

Copyright © 1996-2020 Information Builders