All Apps and Add-ons

More DB Connect timestamp parsing trouble

wegscd
Contributor

I looked at https://answers.splunk.com/answers/288255/splunk-db-connect-2-how-to-set-timestamp-format-to.html, but nothing there seems to be helping, and since I'm in a SH clustering environment, I don't want to start hand editing inputs.conf...

Using Splunk 6.3.0.1, DB Connect 2.3.0 and DB2 V10.4, I am trying to create an Advanced input. I have a query that is returning a VARCHAR column that has the string representation of a date that I want to use as the timestamp: 2016-08-02 00:00:03:495 GMT.

When I get to the "Configure Timestamp Column", I try to configure the timestamp in the GUI with this as the datetime format: yyyy-MM-dd hh:mm:ss:SSS zzz, but I always get

As selected column is not one of native datetime types (DATE, TIME, TIMESTAMP etc.), you must either specify a format string (in the style of Java's SimpleDateFormat) or cast the column to timestamp manually in your SQL.

alt text

I know that format string is a valid SimpleDateFormat for that data (went as far as to write a test program to ensure my eyes weren't bad), put the column that I want to parse at the end of the list of columns.

I would just have the silly query return a timestamp object, but the strings have timezone data in them, and I can't figure out how to get DB2 to parse the timezone and give me back a timestamp object rather than a string/varchar.

What else to try?

wegscd
Contributor

I am suspecting that the issue is what dolivasoh had in https://answers.splunk.com/answers/288255/splunk-db-connect-2-how-to-set-timestamp-format-to.html; the GUI seems to be a little erratic. After a lot of fooling around, it finally accepted zzzzz in the format string.

Gonna dig into it some more....

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

I think your problem is the milliseconds (zzz).
http://www.java2s.com/Tutorial/Java/0040__Data-Type/SimpleDateFormat.htm

Try S instead?

0 Karma

wegscd
Contributor

SSS is in there. zzz is to pick up the timestamp.

0 Karma

wegscd
Contributor

...and there's the issue. in spite of the fact the zzz is a valid format specifier for SimpleDateFormat (as demonstrated below), DB Connect 2 keeps choking on it.

import java.text.ParseException;
import java.text.SimpleDateFormat;
import org.junit.Test;

public class TestDateParse {

  @Test
  public void test() throws ParseException {
    String c0 = "2016-08-02 00:00:03:495 +0000";
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss:SSS zzz");
    System.out.println(sdf.parseObject(c0));
  }
}
0 Karma

richgalloway
SplunkTrust
SplunkTrust

Can you put to_date() call (or the equivalent for your DB) in your query to force the column to a timestamp?

---
If this reply helps you, Karma would be appreciated.

wegscd
Contributor

DB2 10.4 does not have an equivalent that honors timezone...

0 Karma

richgalloway
SplunkTrust
SplunkTrust

If the time is always GMT then something like this may do the job.

CONCAT(TO_DATE(RTRIM(COMPLETED,'GMT'), 'yyyy-MM-dd hh:mm:ss:SSS '),'Z')
---
If this reply helps you, Karma would be appreciated.
0 Karma

wegscd
Contributor

that gets me a string, but how to parse it? If I put the 'z' pattern into the Datetime format field, then I get the error above.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Once you have a string Splunk can handle, it should parse it for you. If the 'z' causes an error, you can remove the CONCAT command.

---
If this reply helps you, Karma would be appreciated.
0 Karma

wegscd
Contributor

If I remove the 'z', then my dates gets parsed using my local timezone, not GMT; timestamps will be off by 4 or 5 hours, depending on the time of year.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

I'm stumped.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...