topic Re: Time format in DB query result in Getting Data In

Time format in DB query result

anoopambli — Mon, 28 Sep 2020 15:29:32 GMT

I am using splunk DB connect to pull out some data to create a dashboard. But having difficulty in getting the time format corrected in search result. The time format looks like in seconds, how do i convert them to Date-Month-year format. Below is the sample of search result, i am trying to get Creation_field and last_update_field time format adjusted.

CREATION_DATE DESCRIPTION LAST_UPDATE_DATE USERNAME
1384405200 xnje411 server monitoring addition 1385010000 Melvin Bolden (a056648)
1384318800 snjw100 server monitoring addition 1385960400 Melvin Bolden (a056648)

Re: Time format in DB query result

jcoates_splunk — Mon, 16 Dec 2013 17:19:08 GMT

Hi, you probably just need to make sure that Splunk recognizes that's a time. Here's some tips: http://docs.splunk.com/Documentation/DBX/1.1.1/DeployDBX/Configuredatabasemonitoring#About_timestamps_and_database_output

Re: Time format in DB query result

aholzer — Mon, 16 Dec 2013 17:26:14 GMT

Look at the convert command:
http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/convert

Or look at the eval function strftime:
http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/CommonEvalFunctions

Hope this helps

Re: Time format in DB query result

sroback_splunk — Mon, 28 Sep 2020 15:29:53 GMT

You can try to use the | fieldformat command (similar to eval, but applies at field rendering time, so that sort still works correctly) and the strftime() function. For example:

... | fieldformat Creation_field = strftime(Creation_field, “%m-%d-%y”)

See: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Fieldformat#Examples

Re: Time format in DB query result

anoopambli — Tue, 17 Dec 2013 10:10:48 GMT

I tried using fieldformat option but facing some problem. This is the query i am running

... | fieldformat "OPEN_TIME"=strftime('Open time', "%m-%d-%y")

The result for Open_time field coming up as blank now,

Anything i am doing wrong here??

Re: Time format in DB query result

anoopambli — Tue, 17 Dec 2013 15:05:11 GMT

I was able to fix it by using convert command convert timeformat="%b %d, %Y" ctime(OPEN_TIME) AS Open-Date