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     [SOLVED] Timestamp to date conversion

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Timestamp to date conversion
 Login/Join
 
Virtuoso
posted
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,


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: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Gold member
posted Hide Post
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
 
Posts: 64 | Location: North Carolina | Registered: December 04, 2007Report This Post
Virtuoso
posted Hide Post
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?!?


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: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
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...


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: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Expert
posted Hide Post
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


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
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


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
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.


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: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
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!


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: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
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


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: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report 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     [SOLVED] Timestamp to date conversion

Copyright © 1996-2020 Information Builders