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>,

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.

You could set up a define function to do the change, so its simple there then on.

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.

thanks folks. alas, alpha is what I needed to avoid. sigh...

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

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.

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

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.

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');

quote:

It does not however have a function for Time Elapsed.

There was also this thread from Jun 2015

T

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.

quote:

But I'll revisit this in 10 years

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

T

quote:

I shall be retired by then

That will be a great loss the WebFOCUS world.

Thanks for the sentiment Waz 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

You're bringing a tear to my eye.

No, really you are.

Not just the Aussie wind then

T

T...that might work.... stay tuned...

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

quote:

I can't control the y axis enough..

Example?

T

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.)

Time to stop studying and get back to work...

quote:

Time to stop studying and get back to work..

David,

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

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

T

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.

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   
  

Thank you for the comments Waz, Tony, Wep, and Eric.

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

Nice.

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