Splunk DB Connect 2: Why is a MySQL database time column in human readable format showing as epoch in DBX?

[changux]

Hi all.

I have the Splunk DB Connect 2 app working against a MySQL 5.x database. I have two columns with timestamps, in_time and out_time in human readable format YYYY-MM-dd HH:mm:ss (database side). In the db input configuration, the preview of the data (with SQL query) shows the column in_time in epoch time, meanwhile out_time is in human readable format. This is my first question: Why is the column shown as epoch time when it is stored from the source in YYYY-MM-dd HH:mm:ss format?
I checked this similar question:

http://answers.splunk.com/answers/183660/db-connect-why-datetime-field-in-mssql-is-imported.html?utm...

But the answer mentions a stanza that is not working in DB Connect 2. Any help?

Thanks!

[emiller42 · Accepted solution]

I seriously doubt your timestamp is stored in the DB in a human-readable format. Typically, whatever tool you're using to query the DB will display DATE or TIMESTAMP columns formatted based on your locale settings. (Changing your locale settings would change the output)

So as you discovered, an eval to format the data is appropriate here. Another option is fieldformat:

... | fieldformat in_time=strftime(in_time, "%c")

The difference between eval and fieldformat is that the former actually changes the value of the field, and the latter just alters display. This means you can make things look like human-readable date/time stamps, and still do math on them:

... | fieldformat in_time=strftime(in_time, "%c") | fieldformat out_time=strftime(out_time, "%c") | eval duration=out_time-in_time | fieldformat duration=tostring(duration, "duration")

would give you three human-readable columns, but Splunk still treats them as their original decimal values.

[changux]

Finally, i can't find an explanation. The workaround was use an eval function to convert to human readable format.

Thanks a lot.