I'm new to WebFocus. I need to calculate the difference in fractional days between CURRENT DATE/TIME and a field containing the integer number of seconds since the Unix EPOCH (Thur 01 Jan 1970 00:00:00 GMT). In my report this calculation needs to be in a SubQuery. I'm using the "FIELDNAME ... SUB_QUERY ..." syntax which works but is not well documented in the 7.7 manuals. I need to do a "SELECT" in the SUB_QUERY's "JOIN_WHERE" clause. Suggestions?

I can't figure out how to do the date diff calculation in WebFocus. If it was SQL server it would be something like this:
SqlServer date/time == (EpochDT / seconds_per_day) + days_between_01-01-1970_and_01-01-1900
== (EpochDT / 86400.0) + 25567.0

Any suggestions would be appreciated.

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

On just the date difference calculation -

If you want a time element (hours, minutes, seconds) included in your result then look at using HDIFF. Feed your epoch datetime and the current datetime. HDIFF will give you any component difference - check out the help file in Dev Studio for details.

If you are only interested in days or higher then look at using DATEDIF. You will only require the YYMD (or equivalent - MDYY, DMYY etc.) component of your date.

T

Hi,

This is some code that I got from a customer that uses Remedy.

It isn't exactly what you want, but it may give you some ideas. In this example they allow their users to specify a start date and end date on an HTMLFORM. They then convert these dates to the equivalent Epoch date in "seconds" and read the result into amper variables.
They then use these amper variables in a WHERE clause to retrieve data from Remedy for the Start/End date range.

Twanette

   
-***********************************************************
-* LGDMNSEC.fex (PCSR02.FEX)
-* This routine determines the Start, Last and End seconds from
-* the parameters entered at the Prompt.
-***********************************************************
 
DEFINE FILE CAR
-* CREATE FOCUS SMART DATE EQ TO DATE FROM SUPPLIED
 STARTDATE/DMYY WITH COUNTRY = '&DATEFROM' ;
 STARTDOW/W     = STARTDATE ;
-* CREATE FOCUS SMART DATE EQ TO DATE END SUPPLIED
 ENDDATE/DMYY   WITH COUNTRY = '&DATEEND' ;
 ENDDOW/W       = ENDDATE ;
-* NUMBER OF DAYS SINCE 1901
 STARTDAYS/I9  = STARTDATE ;
-* NUMBER OF DAYS SINCE 1901
 ENDDAYS/I9    = ENDDATE ;
-* SUBTRACT NUMBER OF DAYS FROM 1970 TO 1901
 ARSSTART/I9   = STARTDAYS - 25203 ;
-* SUBTRACT NUMBER OF DAYS FROM 1970 TO 1901
 ARSEND/I9     = ENDDAYS   - 25203 ;
-* DETERMINE # OF SECONDS ELAPSED SINCE 1970 TO DATE
 STARTSECS/D15  = (ARSSTART * 86400) -7200;
-* DETERMINE # OF SECONDS ELAPSED SINCE 1970 TO DATE
 LASTSECS/D15   = ARSEND * 86400 ;
-* DETERMINE # OF SECONDS ELAPSED SINCE 1970 TO DATE
 ENDSECS/D15    = ((ARSEND) * 86400) +79200 -1;
END
TABLE FILE CAR
PRINT STARTSECS LASTSECS ENDSECS
IF READLIMIT IS 1
ON TABLE SAVE AS SLA01BB
END
-RUN
 
-READ SLA01BB &X1.A5 &START.A10 &X2.A5 &LAST.A10 &X3.A5 &END.A10
-TYPE START &START 
-TYPE LAST &LAST 
-TYPE END &END
-TYPE ********** END of Date Conversion Routine (LGDMNSEC) *************

Just BTW - as per Tony's feedback, you could obviously HDIFF to get similar results.