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?
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....
I think your problem is the milliseconds (zzz).
http://www.java2s.com/Tutorial/Java/0040__Data-Type/SimpleDateFormat.htm
Try S instead?
SSS is in there. zzz is to pick up the timestamp.
...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));
}
}
Can you put to_date()
call (or the equivalent for your DB) in your query to force the column to a timestamp?
DB2 10.4 does not have an equivalent that honors timezone...
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')
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.
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 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.
I'm stumped.