I found an error in the procedure in my post "Reportcaster job activity", in relation with how timestamps are converted to date-time.

The RCaster database stores date-times as long. For example, a START_STAMP is stored as "1,334,554,346,693", which translates to "2012-04-16 07:32:26.693".

However, it turns out that the calculation to convert these values forgets to calculate in the fact that the EPOCH base-timestamp is based in GMT and not in our local time zone!
The value I get from my calculation is: 2012-04-16 05:32:26.693 - two hours off!

The calculation I currently use is:

SET DTSTANDARD = STANDARDU

DEFINE FILE BOTLOG
 EPOCH/HYYMDs	= DT(1970-01-01T00:00:00.00);

 TS_STR/HYYMDUs	= HADD(EPOCH, 'ms', START_STAMP, 32, TS_STR);
 TS_END/HYYMDUs	= HADD(EPOCH, 'ms', END_STAMP, 32, TS_END);
END

How do I base EPOCH at GMT, with the other dates still based in local time?

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

Maybe below example will give some idea ontimezone conversion.
DEFINE FILE CAR
ATLANTA_GMT/I3 = -5 ;
LONDON_GMT/I3 = 0 ;
DALLAS_GMT/I3 = -6 ;
SYDNEY_GMT/I3 = +10 ;
ATLANTA_TIME/HYYMDI =
HINPUT(12,'200501150900',8,'HYYMDI');
LONDON_TIME/HYYMDI =
HADD(ATLANTA_TIME,'HOUR',(-ATLANTA_GMT +LONDON_GMT),8,'HYYMDI') ;
DALLAS_TIME/HYYMDI =
HADD(ATLANTA_TIME,'HOUR',(-ATLANTA_GMT +DALLAS_GMT),8,'HYYMDI') ;
SYDNEY_TIME/HYYMDI =
HADD(ATLANTA_TIME,'HOUR',(-ATLANTA_GMT +SYDNEY_GMT),8,'HYYMDI') ;
END
TABLE FILE CAR
PRINT CAR
ATLANTA_TIME
LONDON_TIME
DALLAS_TIME
SYDNEY_TIME
END

Thanks
Krish

7703,
UNIX
PDF,EXCEL

That's not what I'm looking for, you just hard-coded those time-zones (w/o DST!) in your procedure.

The problem I'm facing is that EPOCH in GMT is a different date/time in local time depending on our DST.

Isn't there some built-in function to convert between those?!?

I found this "solution" in ECL: https://techsupport.informatio...utions/80091072.html

But that totally gets it wrong! You can't convert GMT to CET/CEST like that - GMT doesn't have daylight saving time (that's the whole point of it, after all), while CET/CEST do.

I'll keep on looking...

If you are running in Windows, this will get the GMT offset.

DEFINE FUNCTION F_HX2NUM/I9 (HEXChar/A1)
 HexCHAR/A1 = UPCASE(1, HEXChar, HexCHAR) ;
 F_HX2NUM/I9 = DECODE HexCHAR('0'  0 '1'  1 '2'  2 '3'  3 '4'  4 '5'  5 '6'  6 '7'  7
                              '8'  8 '9'  9 'A' 10 'B' 11 'C' 12 'D' 13 'E' 14 'F' 15 ELSE 0) ;
END

DEFINE FUNCTION F_HX2PK/P20 (HEXWORD/A16)
 T_HX2PK/P20 = 0
             + F_HX2NUM(EDIT(HEXWORD,'9$$$$$$$')) * 268435456
             + F_HX2NUM(EDIT(HEXWORD,'$9$$$$$$')) * 16777216
             + F_HX2NUM(EDIT(HEXWORD,'$$9$$$$$')) * 1048576
             + F_HX2NUM(EDIT(HEXWORD,'$$$9$$$$')) * 65536
             + F_HX2NUM(EDIT(HEXWORD,'$$$$9$$$')) * 4096
             + F_HX2NUM(EDIT(HEXWORD,'$$$$$9$$')) * 256
             + F_HX2NUM(EDIT(HEXWORD,'$$$$$$9$')) * 16
             + F_HX2NUM(EDIT(HEXWORD,'$$$$$$$9'))
             ;
 F_HX2PK/P20 = IF T_HX2PK GT 1440 THEN 4294967296 - T_HX2PK ;
END

-* system call to write the registry value to a file
! REG QUERY HKLM\SYSTEM\CURRENTCONTROLSET\CONTROL\TIMEZONEINFORMATION /V ACTIVETIMEBIAS > TZ.TXT

-* filedef the file generated so that it can be read using a -READ

FILEDEF TZ DISK ./TZ.TXT (LRECL 80 RECFM V
-RUN

-* loop through the file to find the line that contains the required value
-LOOP

-READ TZ &LINE.A80.

-IF &LINE OMITS 'ACTIVETIMEBIAS' GOTO LOOP;

-RUN

-* extract the value using GETTOK
-SET &HEX=GETTOK(&LINE, 128, -1, 'x', 10, 'A10');

-SET &Hex = SUBSTR(16,RJUST(16,'00000000' | &HEX,'A16'),9,16,8,'A8') ;

-SET &GMTOffset = F_HX2PK(&Hex) ;

-TYPE GMT Offset = &GMTOffset Minutes

Here is another way that uses JAVA to get the offset.

FILEDEF TZJAVA DISK getrawoffsettimezone.java
-RUN
-WRITE TZJAVA import java.util.*;
-WRITE TZJAVA class getrawoffsettimezone {
-WRITE TZJAVA     public static void main( String args[] ){
-WRITE TZJAVA         Date date = new Date();
-WRITE TZJAVA         // Create object of TimeZone class. 
-WRITE TZJAVA         TimeZone obj = TimeZone.getDefault();
-WRITE TZJAVA         // getRawOffset() method call for specified time zone.
-WRITE TZJAVA         int offset = 0 ;
-WRITE TZJAVA         if (obj.useDaylightTime() && obj.inDaylightTime(date)) {
-WRITE TZJAVA             offset = obj.getRawOffset() + obj.getDSTSavings();
-WRITE TZJAVA         }
-WRITE TZJAVA         else {
-WRITE TZJAVA             offset = obj.getRawOffset();
-WRITE TZJAVA         }
-WRITE TZJAVA         System.out.println(offset);
-WRITE TZJAVA   }
-WRITE TZJAVA }

! javac getrawoffsettimezone.java
! java -classpath . getrawoffsettimezone > TZ.TXT

-RUN

FILEDEF TZ DISK ./TZ.TXT (LRECL 80 RECFM V
-RUN

-READ TZ &LINE.A80.

-* Convert to minutes
-SET &GMTOffset = (-1) * &LINE.EVAL / 60 / 1000 ;

-TYPE GMT Offset = &GMTOffset

Golly, those are some interesting solutions! Thanks for posting that.

However, since I'm reading those integer timestamp values from a database, I'll just ask the database for the correct value. That's probably a lot easier...

I find it rather disappointing that WebFOCUS can't handle converting between different time zones of itself. It's got to be one of a few products that can't - heck, I think even my toaster can, but my toaster isn't very good at reporting.

I ended up using the difference between GETDATE() and GETUTCDATE() in MSSQL to calculate the local time zone offset relative to GMT:

 SELECT
	CAST(START_STAMP AS BIGINT)/1000 AS START_TS,
	CAST(END_STAMP AS BIGINT)/1000 AS END_TS,
	DATEDIFF(s, '1970/01/01 00:00:00', DATEADD(dd, 0, DATEDIFF(dd, 0, GETUTCDATE()))) AS TODAY_TS,
	DATEDIFF(s, '1970/01/01 00:00:00', DATEADD(dd, 0, DATEDIFF(dd, 0, GETUTCDATE())) +1) AS TOMORROW_TS,
	DATEDIFF(s, GETUTCDATE(), GETDATE()) AS GMTOFFSET,
	JOB_DESC
  FROM BOTLOG
 WHERE ...

Note that START_STAMP converted to an integer type is larger than what an MSSQL int (4 bytes) can contain, so you need to cast to BIGINT (8 bytes) to prevent errors.

I put the above in a CTE (common table expression) for ease of use, but that requires adding the below setting to your procedure (or edasprof):

ENGINE SQLMSS SET CURSORS CLIENT

None of this was easy to find!

Here's an update with a purely WebFOCUS based solution, now that we have HGETC and HGETZ in WF8.1.
I'm using some of the simplified datetime functions from that release, but they easily translate to their less simple alternatives.

I abuse SYSTABLE as a dummy table to give me a single record that I don't use otherwise, it would make sense to use BOTLOG for this particular situation.

-SET &TS_BOTLOG	= 1334554346693;

DEFINE FILE SYSTABLE
	TS/P20.6	= &TS_BOTLOG/1000;
	EPOCH_LOCAL/HYYMDm = DT('1970-01-01');
	EPOCH/HYYMDm = DTADD(EPOCH_LOCAL, MINUTE, DTDIFF(HGETC(8, 'HYYMDI'), HGETZ(8, 'HYYMDI'), MINUTE));
END
TABLE FILE SYSTABLE
PRINT
	EPOCH_LOCAL
	EPOCH
	TS
	COMPUTE TSDT/HYYMDm = DTADD(EPOCH, SECOND, TS);

BY NAME NOPRINT
IF READLIMIT EQ 1
END

  EPOCH_LOCAL                 EPOCH                                         TS  TSDT                                              
  -----------                 -----                                         --  ----                                              
  1970/01/01 00:00:00.000000  1970/01/01 02:00:00.000000     1334554346,693000  2012/04/16 07:32:26.000000