Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]is there a Time-of-day format?
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]is there a Time-of-day format?
 Login/Join
 
Expert
posted
I needa field format that will take a number of minutes, eg, 810, and express it as a time of day, eg 13:30.
something like TOD/HM= 810 ;
tableau has it. bo has it. do we??

This message has been edited. Last edited by: <Emily McAllister>,




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
I think you need to use HADD with a base date set to Zero Hours.

Internally I'm not sure that format DateTime fields are, so, don't like your chances on just assigning it.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6270 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
You could set up a define function to do the change, so its simple there then on.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6270 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
HADD sort of works, unless you have durations that span more than 24 hours.

When that happens, hours wrap back to 0 and you also need to print the number of days. Unfortunately, when using datetime format to print a duration you're dealing with dates. Dates always start at the 1st of a month, so for 810 minutes you end up with "1 13:30" instead of "0 13:30". For 2250 minutes you end up with "2 13:30" instead of "1 13:30", etc.

And even if you fix that, the result is still a bit cryptic to read for end users...

I suppose that's why some RDBMSes have an interval type for the difference between two date-times (then again, not many database connection libraries can deal with such types properly anyway).

In the end, we ended up converting the duration to an alpha field with the number of hours concatenated with the remaining number of minutes, resulting in "13:30" and "37:30" for the respective example durations.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1663 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
thanks folks. alas, alpha is what I needed to avoid. sigh...




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
Of course there is -

TABLE FILE GGSALES
  SUM COMPUTE MYHDATE/HYYMDS = HADD(HINPUT(8, DATECVT(DATE, 'I8YYMD', 'A8YYMD'), 8, 'HYYMDS'), 'MINUTES', 810, 8, 'HYYMDS');
      COMPUTE MYHTIME/HHI    = HHMS(MYHDATE, 8, 'HHI');
   BY DATE
END


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: 5681 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Master
posted Hide Post
As Tony shows, yes WF has a format for time of day. It does not however have a function for Time Elapsed. I remember looking for that a few months ago and not really seeing anyone that has that.


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, 2013Reply With QuoteReport This Post
Master
posted Hide Post
quote:
As Tony shows, yes WF has a format for time of day. It does not however have a function for Time Elapsed.

Does this thread help?
http://forums.informationbuild...457092626#7457092626
 
Posts: 806 | Registered: April 23, 2003Reply With QuoteReport This Post
Master
posted Hide Post
Thanks for the post David,

If I end up needing to do a tablature report with Time Elapsed, I'll definitely use that post. It'll save me the time of trying to figure it out myself. Unfortunately That doesn't work for graphing, which is what I needed it for. Maybe I can work some trickery to make mask the Y axis or something. But I haven't really been able to find a good way to graph elapsed time. This is where a data type would come in handy.... But I don't have 10 years to wait for some one to find time to read such an NFR... so not going to waste my time asking for it.


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, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
At the time I posted this its was working. (WF 7.6)

May still work.

DT_NULL/HHIS = HINPUT(14, '00000000000000000', 8, 'HHIS');
DT_DIFF/HHIS = HADD(DT_NULL, 'SECOND',MyNumberOfSeconds, 8, 'HYYMDS');


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6270 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
It does not however have a function for Time Elapsed.

There was also this thread from Jun 2015

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: 5681 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Master
posted Hide Post
Tony,

You are absolutely correct. In what I was working on though, I was creating graphs, not tablature reports. So I guess, more accurately... I would have needed a date format like Susannah is talking about in this thread. You can't graph Alpha values is the problem. They are all just counts. But I'll revisit this in 10 years when IBI decides to consider that NFR Doug put in. Only 9 more years till we get it.


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, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
But I'll revisit this in 10 years

I shall be retired by then Smiler ... but probably still visiting the forum Wink

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: 5681 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
I shall be retired by then


That will be a great loss the WebFOCUS world.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6270 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Thanks for the sentiment Waz Smiler There are many members of the forum to whom that also applies!

It is good that there are a few younger members that are becoming revered in their own right. I have recently had the pleasure of meeting a few of them and the future appears to be assured! My best wishes to each and every one of them!

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: 5681 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
Sweating

You're bringing a tear to my eye.

No, really you are.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6270 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Not just the Aussie wind then Wink

T
 
Posts: 5681 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
T...that might work.... stay tuned...




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
ah rats, I can't control the y axis enough..
but I might well use it for the display table.
its cooler than minutes/60 : the imod




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
I can't control the y axis enough..

Example?

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: 5681 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Master
posted Hide Post
This has the making of another great FP thread...interesting business need, excellent ideas, and a dash of humor. :-)

I think a consensus of the requirements are something like:
* incoming date-time fields
* need visualization with y-axis representing duration in hours:minutes.

Here is something I've been working on, as I think I might have this kind of requirement in the near future.

-* File yaxistime.fex
-*
-* Extract data from database.  
-*
DEFINE FILE GGSALES
-* Not part of the technique; create data for testing.
 NAME/A20          = DECODE SEQ_NO (1 'Francis' 2 'Tony' 3 'David' 4 'Waz');  
 DATE_START/HYYMDS = DT(2005/12/26 05:45:05.000);
 DATE_END/HYYMDS   = IF SEQ_NO EQ 1 THEN DT(2005/12/26 08:46:06.000) ELSE 
                      IF SEQ_NO EQ 2 THEN DT(2005/12/26 07:47:06.000) ELSE
                       IF SEQ_NO EQ 3 THEN DT(2005/12/26 07:47:06.000) ELSE
		        DT(2005/12/26 16:00:00.000)  ;
END                          
TABLE FILE GGSALES
PRINT   SEQ
	NAME
COMPUTE MN_DIFF/I9  = HDIFF(DATE_END,DATE_START,'MINUTE','I9'); 
-*
IF RECORDLIMIT EQ 4
ON TABLE HOLD AS HLDTIME
END
-RUN
-* 
-* Create visualization.  
-*
GRAPH FILE HLDTIME
SUM MN_DIFF AS 'Time Spent Studying'
BY  NAME    AS 'Student'
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *
*GRAPH_JS
 title: {text: 'Study Hall Report', visible: true},
 subtitle: {text: 'Hours:Minutes Spent Studying Per Student', visible: true}
*END
*GRAPH_JS
 yaxis: {
  numberFormat: function(n){ var hours = Math.floor(Math.abs(n) / 60);
                             var minutes = Math.abs(n) % 60;
			     minutes = (minutes < 10) ? '0' + minutes  : minutes
                             return hours + ':' + minutes;
						   },
  intervalMode: 'interval',
  intervalValue: 60
 }
*END
*GRAPH_JS
 series: [
  {series: 'reset', tooltip: function(v, s, g) { var hours = Math.floor(Math.abs(v) / 60);
                                                 var minutes = Math.abs(v) % 60;
						 minutes = (minutes < 10) ? '0' + minutes  : minutes
						 var time = hours + ':' + minutes;
                                                 return 'Name: ' +  this.getGroupLabel(g) + 
                                                        '<br/>Time: ' + time;}}
 ]
*END
ENDSTYLE
END   


Basically, the process is to draw the bars using minutes, then reformat the axis labels to 'HH:MM'.

All suggestions welcome, as always. (For example, I'd probably want to put the JS calculations in functions, that could be called.)
 
Posts: 806 | Registered: April 23, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
Time to stop studying and get back to work...


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6270 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Master
posted Hide Post
quote:
Time to stop studying and get back to work..

Music
 
Posts: 806 | Registered: April 23, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
David,

Nice method and very similar idea to something I have tried to do on date reformatting for Gantt charts (needs more work Frowner).

Unlike Waz, I guess that I need to study a little more! Wink

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: 5681 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Solid idea David; it will sort correctly due to the value being in minutes and it can handle values exceeding 24 hours.

I get the feeling that it should be possible to turn this into SVG format somehow so that we can also use this in, say, PDF output...

I know from experience that web browsers can make that conversion, but doing that server-side could be a challenge.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1663 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Master
posted Hide Post
David,

I think I owe you a beer! One of these days I'm going to have to meet you at the bar after work! I spent a lot of time trying to figure out how to get this to work!

-* File yaxistime.fex
-*
-* Extract data from database.  
-*
DEFINE FILE GGSALES
-* Not part of the technique; create data for testing.
 NAME/A20          = DECODE SEQ_NO (1 'Francis' 2 'Tony' 3 'David' 4 'Waz');  
 DATE_START/HYYMDS = DT(2005/12/26 05:45:05.000);
 DATE_END/HYYMDS   = IF SEQ_NO EQ 1 THEN DT(2005/12/26 08:46:06.000) ELSE 
                      IF SEQ_NO EQ 2 THEN DT(2005/12/26 07:47:06.000) ELSE
                       IF SEQ_NO EQ 3 THEN DT(2005/12/27 07:47:06.000) ELSE
		        DT(2005/12/26 16:00:00.000)  ;
END                          
TABLE FILE GGSALES
PRINT   SEQ
	NAME
COMPUTE MN_DIFF/I9  = HDIFF(DATE_END,DATE_START,'MINUTE','I9'); 
-*
IF RECORDLIMIT EQ 4
ON TABLE HOLD AS HLDTIME
END
-RUN
-* 
-* Create visualization.  
-*
GRAPH FILE HLDTIME
SUM MN_DIFF AS 'Time Spent Studying'
BY  NAME    AS 'Student'
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *
*GRAPH_JS
 title: {text: 'Study Hall Report', visible: true},
 subtitle: {text: 'Hours:Minutes Spent Studying Per Student', visible: true}
*END
*GRAPH_JS
 yaxis: {
  numberFormat: function(n){ var days = Math.floor(Math.abs(n) / 60 / 24);
  							 var hours = Math.floor(Math.abs(n) / 60);
                             var minutes = Math.abs(n) % 60;
				 hours = ( hours > 24 ) ? hours - (days * 24) : hours ;
				 days = ( days < 1 ) ? '' : days + 'd ' ;
			     minutes = (minutes < 10) ? '0' + minutes  : minutes
                             return days + hours + ':' + minutes;
						   },
  intervalMode: 'interval',
  intervalValue: 60
 }
*END
*GRAPH_JS
 series: [
  {series: 'reset', tooltip: function(v, s, g) { var days = Math.floor(Math.abs(v) / 60 / 24);
  												 var hours = Math.floor(Math.abs(v) / 60);
                                                 var minutes = Math.abs(v) % 60;
				 		 hours = ( hours > 24 ) ? hours - (days * 24) : hours ;
				 	 	 days = ( days < 1 ) ? '' : days + 'd ' ;
						 minutes = (minutes < 10) ? '0' + minutes  : minutes
						 var time = days + hours + ':' + minutes;
                                                 return 'Name: ' +  this.getGroupLabel(g) + 
                                                        '<br/>Time: ' + time;}}
 ]
*END
ENDSTYLE
END   
  


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, 2013Reply With QuoteReport This Post
Master
posted Hide Post
Thank you for the comments Waz, Tony, Wep, and Eric. Nice Thread
 
Posts: 806 | Registered: April 23, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
So, being inspired by David's time reformatting of the yaxis using JS, I thought that, once I had a little time, I would revisit what I was doing for a Gantt chart.

FWIW, this is a revision of an example that I posted in December 2005 in the hope that it helps someone out.

As usual, the first section builds some sample data so that anyone can try this without having to have prepared data.

SET HOLDLIST = PRINTONLY
APP FI GANTT DISK GANTT.MAS (LRECL 80
-RUN
-WRITE GANTT
-WRITE GANTT FILE=GANTT,SUFFIX=FOC
-WRITE GANTT SEGNAME=SEG1
-WRITE GANTT FIELD=AREA,,A15,A15,$
-WRITE GANTT FIELD=TASK,,A27,A27,$
-WRITE GANTT FIELD=BEGDATE,,YYMD,YYMD,$
-WRITE GANTT FIELD=ENDDATE,,YYMD,YYMD,$
-RUN

CREATE FILE GANTT
MODIFY FILE GANTT
FIXFORM AREA/A15 TASK/A27 BEGDATE/A8 ENDDATE/A8
DATA
Engineering     1 Site Survey             2000101820001115
Engineering     2 Draft Survey Results    2000101820001204
Engineering     3 GMBH Review             2000120420001229
Engineering     4 Survey RPT (Final)      2001010120010122
Engineering     5 GMBH RFV Approval       2001010320010206
Engineering     6 System Design           2001021420010320
Engineering     7 HMBC Review             2001032020010404
Engineering     8 Bid and Award           2001040420010604
Engineering     9 Construction Support    2001042720010905
END
-RUN
DEFINE FILE GANTT
  CURRDATE/YYMD   = 20010113;
  CURRTIME/HYYMDs = HDTTM(&YYMD, 8, 'HYYMDS');
END
TABLE FILE GANTT
  SUM MAX.ENDDATE/I8
      MIN.BEGDATE/I8
      MAX.CURRDATE/I8
      CURRTIME
   ON TABLE SAVE FORMAT ALPHA
END
-RUN

-READ SAVE X4 &MaxDate.A8. X4 &MinDate.A8. X4 &CurrDate.A8. &CurrTime.A17.
-SET &MinDate = &MinDate - 14;
-SET &MaxDate = &MaxDate + 14;
-SET &CurrDate = &CurrDate - &MinDate + 28;
-RUN

DEFINE FILE GANTT ADD
  OFFSET/I9     = 14;
  DURATION/I9   = ENDDATE - BEGDATE;
  REMAIN/I9     = IF ENDDATE LT CURRDATE THEN 0
             ELSE IF CURRDATE FROM BEGDATE TO ENDDATE THEN ENDDATE - CURRDATE
             ELSE DURATION ;
  BEGIN/I9      = BEGDATE - &MinDate + 14;
  COMPLETED/I9  = IF ENDDATE LT CURRDATE THEN DURATION
             ELSE IF CURRDATE FROM BEGDATE TO ENDDATE THEN CURRDATE - BEGDATE
             ELSE 0 ;
END

GRAPH FILE GANTT
SUM OFFSET
    BEGIN
    COMPLETED AS 'Completed'
    REMAIN    AS 'Remaining'
BY TASK
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *
*GRAPH_SCRIPT
 setGraphType(25);
 setTransparentFillColor(getSeries(0), true);
 setTransparentBorderColor(getSeries(0), true);
 setSeriesLabel(0, "");
 setTransparentFillColor(getSeries(1), true);
 setTransparentBorderColor(getSeries(1), true);
 setSeriesLabel(1, "");
*END
*GRAPH_JS
-* The base date for JavaScript is 01/01/1970 and for WebFOCUS 31/12/1900
-* so we need to subtract the difference in days to obtain the correct basedate for our chart
-* and then convert it to milliseconds
 yaxis: {
  numberFormat: function(n){ var daynum = (n + &MinDate - 25203) * 24 * 60 * 60 * 1000;
                             var minDate = new Date(daynum);
                             return (minDate.getDate() + "/" + (minDate.getMonth() + 1) + "/" + minDate.getFullYear());
                           },
  intervalMode: 'interval',
  intervalValue: 28
 },
series: [
  {series: 'reset', tooltip: function(v, s, g) { return this.getGroupLabel(g) + 
                                                        "<br/>" + v + " days " + this.getSeries(s).label;}}
 ],
referenceLines: [
  {value: &CurrDate,
   axis: 'y',
   line: {color: 'red',
          width: 1,
          dash: '6 2 6'},
   label: {text: 'Current Date:',
           font: 'bold 8pt Sans-Serif',
           color: 'red'},
   anchor: 'end',
   showValue: false
   }
  ]
*END
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: 5681 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
Nice.

Although, being on Linux, I had to change the case of GANTT.MAS


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6270 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]is there a Time-of-day format?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.