topic Re: db connect timestamp conversion in Getting Data In

db connect timestamp conversion

aaronkorn — Tue, 16 Apr 2013 20:34:00 GMT

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?

Re: db connect timestamp conversion

Dan — Tue, 16 Apr 2013 21:32:59 GMT

In Splunk, add: | convert ctime(Update_Time)

Re: db connect timestamp conversion

aaronkorn — Wed, 17 Apr 2013 12:20:54 GMT

excellent! Thank you

Re: db connect timestamp conversion

ihayesjr — Fri, 14 Jun 2013 15:28:29 GMT

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

Re: db connect timestamp conversion

Dan — Mon, 28 Sep 2020 14:05:47 GMT

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

Re: db connect timestamp conversion

zsteinkamp_splu — Mon, 16 Oct 2017 18:14:18 GMT

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.