jarivaahtera Absent Member.
Absent Member.
867 views

Timezone handling in parser

I'm having difficulties with handling timezones with a MSSQL database parser. The situation is that the timestamps in the database are in UTC time so it is 2 hours behind our time. When i try to save it in our time to the setObserverEventTime i get only some error codes and non of my code variations seem to work. We are using Sentinel version 8.1.1.0_4309 and Plugin-SDK-2014.1r1-Beta-36 if it matters. The codes i've been using are for example this:


// Date & Time comes from the database in this form: 2016-08-08 02:01:44.0 or the number after dot can be three digit long so we have to trim the milliseconds off
var dttime = this.RXMap.col_START_DATE.replace(/\.\d*/, '');
e.setObserverEventTime(DateTime.parseExact(dttime,"yyyy-MM-dd HH:mm:ss", this.s_TimeZone));


This results in error:

Parsing failed: JavaException: java.lang.NumberFormatException: For input string: "null"; original message:

I also checked it with Debug Collector and there DateTime says TypeError: Cannot find default value for object.

I get the same NumberFormatException with this code:


var dttime = this.RXMap.col_START_DATE.replace(/\.\d*/, '');
var sub_dttime = DateTime.parse(dttime, this.s_TimeZone);
e.setObserverEventTime(sub_dttime);


I also tried to use external date.js and locale.js files but the result still is not successfull. However i would like to get this timezone setting done with Sentinel datetime handling if possible.

Could somebody also tell me what is the difference between DateTime.parse and DateTime.parseExact and when to use them? Is there any documentation about this time handling in Sentinel?
0 Likes
6 Replies
jarivaahtera Absent Member.
Absent Member.

Re: Timezone handling in parser

I also noticed that in the Sentinel 8.1 release notes it states that the Java version has also been upgraded to 8. Could that affect those datetime functions?
0 Likes
Highlighted
Knowledge Partner
Knowledge Partner

Re: Timezone handling in parser

I would not think that Java's version would impact this, but it is
possible I guess.

In my case using your code in the debugger created within Eclipse (for
development) the parseExact line you are using seems to work:


DateTime.parseExact(newtime, "yyyy-MM-dd HH:mm:ss", '');
2016 August 8 02:01:44 UTC+0000

DateTime.parseExact(newtime, "yyyy-MM-dd HH:mm:ss", '-0600');
2016 August 8 02:01:44 UTC-0600


You may also just want to take out your search/replace and instead capture
the millisecond portion with the parseExact (notice the '.S' at the end:


DateTime.parseExact(newtime, "yyyy-MM-dd HH:mm:ss.SSS", '');
2016 August 8 02:01:44 UTC+0000


In my case the debugger above is using Java 1.8, so that would seem to
rule out Java version issues.

Are you able to duplicate this in the debugger? What value do you have
for this.s_TimeZone in your example? If you take he DateTime.parseExact()
call out of the setObserverEventTime method, does it still return null, I
presume? It may be good to break it out so you can see each method on its
own.


--
Good luck.

If you find this post helpful and are logged into the web interface,
show your appreciation and click on the star below.

If you want to send me a private message, please let me know in the
forum as I do not use the web interface often.
0 Likes
brandon-langley Absent Member.
Absent Member.

Re: Timezone handling in parser

jarivaahtera;2482841 wrote:
I'm having difficulties with handling timezones with a MSSQL database parser. The situation is that the timestamps in the database are in UTC time so it is 2 hours behind our time. When i try to save it in our time to the setObserverEventTime i get only some error codes and non of my code variations seem to work. We are using Sentinel version 8.1.1.0_4309 and Plugin-SDK-2014.1r1-Beta-36 if it matters. The codes i've been using are for example this:


// Date & Time comes from the database in this form: 2016-08-08 02:01:44.0 or the number after dot can be three digit long so we have to trim the milliseconds off
var dttime = this.RXMap.col_START_DATE.replace(/\.\d*/, '');
e.setObserverEventTime(DateTime.parseExact(dttime,"yyyy-MM-dd HH:mm:ss", this.s_TimeZone));


This results in error:

Parsing failed: JavaException: java.lang.NumberFormatException: For input string: "null"; original message:

I also checked it with Debug Collector and there DateTime says TypeError: Cannot find default value for object.

I get the same NumberFormatException with this code:


var dttime = this.RXMap.col_START_DATE.replace(/\.\d*/, '');
var sub_dttime = DateTime.parse(dttime, this.s_TimeZone);
e.setObserverEventTime(sub_dttime);


I also tried to use external date.js and locale.js files but the result still is not successfull. However i would like to get this timezone setting done with Sentinel datetime handling if possible.

Could somebody also tell me what is the difference between DateTime.parse and DateTime.parseExact and when to use them? Is there any documentation about this time handling in Sentinel?


I see you're on the right version of the SDK - for documentation around the APIs, just pull down the JSAPI zip file that is in the same place on the Developer Kit page you downloaded the SDK from.

Using replace is rarely the answer and always leads to tears - also, one thing we've found is using replace has an order of magnitude impact on parser performance. I suspect the replace isn't behaving the way you expect, and if I'm not mistaken is causing at very least the first numerical field to be chopped off.

Now, I suspect you may be trying to chop off the Milliseconds because of this: https://stackoverflow.com/questions/7159895/simpledateformat-is-not-parsing-the-milliseconds-correctly

There's a couple useful things to be aware of:

1) Make sure that time is not a UTC timestamp if you're applying a TZ. Applying a TZ in the DateTime object when the original date is in UTC will create a skew of that many hours.
2) It's much, much better to use a conversion function in the SQL query to get Epoch time (a big integer value) OR to use the conversion functions to render a string date that matches your desired conversion format (instead of crappy replace) - some useful calls are here - http://www.sql-server-helper.com/tips/date-formats.aspx. Epoch is not included in this URL, but there's a lot of opinions on how to render it out there; since I haven't vetted the methods or the sites, I'll leave that decision to you.
3) Do NOT for any reason use suboffset functionality. Use a 'between' expression in your SQL query syntax, and backdate by some set number of seconds. Suboffset queries cause an unfortunate performance penalty and we've learned that we can use a single offset/query and render a MUCH more efficient query into SQL. If you want to look at how we do this, look at the latest Preview of the Oracle DB collector.
0 Likes
jarivaahtera Absent Member.
Absent Member.

Re: Timezone handling in parser

Thanks for the replies.

Tried it like you suggested and deleted the replace row from code and added milliseconds to the parseExact:
var sub_dttime = DateTime.parseExact(this.RXMap.col_START_DATE, "yyyy-MM-dd HH:mm:ss.SSS", '+02:00');
e.setObserverEventTime(sub_dttime);


This doesn't do anything for the time. The parsing is continued and event is sent forward (i see this error in object DateTime: TypeError: Cannot find default value for object) but the event is saved with the current time.

I'm using the Control Center debugger as i never got the SDK debugger working.

The s_TimeZone contains this text: Europe/Helsinki and is defined at the Event Source General settings: Time Zone.

I'm beginning to suspect that there is something wrong with our Sentinel setup. This environment where i'm testing is few months old and was originally installed with some older 8.x version and i upgraded it recently to that 8.1.1.0_4309 version.
0 Likes
jarivaahtera Absent Member.
Absent Member.

Re: Timezone handling in parser

Tried to edit my previous post but it is not possible so i'll continue here.

The event is sent forward if i run it through the debugger but if i start the event source normally without debugger the parsing fails with this error message to the collector_mgr0.0.log:

Parsing failed: JavaException: java.lang.NumberFormatException: For input string: "null"; original message:

The code is still the same as above.
0 Likes
jarivaahtera Absent Member.
Absent Member.

Re: Timezone handling in parser

Found the cause of this problem. My SDK is somehow corrupted i guess. I took the NetIQ made MS SQL parser and added my code to it and now it works. No more errors and the events are saved with the correct time.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.