Time format in DB query result

[anoopambli]

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)

[sroback_splunk]

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

[anoopambli]

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

[anoopambli]

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??

[aholzer]

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

[jcoates_splunk]

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_timestamp...