More DB Connect timestamp parsing trouble

[wegscd]

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.

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]

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....

[jcoates_splunk]

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

Try S instead?

[wegscd]

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

[wegscd]

...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));
  }
}

[richgalloway]

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

[wegscd]

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

[richgalloway]

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')

[wegscd]

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.

[richgalloway]

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.

[wegscd]

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.

[richgalloway]

I'm stumped.