db connect timestamp conversion

[aaronkorn]

Hello,

We are running queries directly in the splunk db connect and not doing an input but the timestamps are getting reformatted and there is no obvious correlation between the two. For example: the query in SQL returns the Update_Time field as 2013-04-10 10:11:50 (yes it is set as a date/time field) but when I run the same query in splunk db connect it returns 1365603110.000. Any ideas how to reformat it?

[zsteinkamp_splu]

To use a DB result field as the event time, then do this:
| dbxquery connection=your.db.connection query="SELECT createdAt, name FROM some_table" | eval _time=strptime(createdAt, "%Y-%m-%d %H:%M:%S") | timechart span=7d count by name
This assumes date/time fields come back from your DB like 2017-10-16 16:20:00.

[Dan · Accepted solution]

In Splunk, add: | convert ctime(Update_Time)

[Dan]

| bucket Update_Time span=2m | stats count by Update_Time

[ihayesjr]

This is helpful, but how do I create a time chart after doing this?

[aaronkorn]

excellent! Thank you