How to able Formatting _time to be used in the timepicker?

[elomotanpru]

Hi everyone,

Pretty new to Splunk and would really appreciate your insight on my current project. Currently creating a dashboard where I want to use a timepicker to change the values in my charts depending on the time period selected by the user via the Date Range - Between.

Currently experiencing problems formatting my _time value to include DATE and eventHour together. Below is my search query and search result for reference.

Thank you in advance.

index=mainframe-platform sourcetype="mainframe:cecmaverage" EXPRSSN = D7X0
| dedup DATE EXPRSSN MIPS
| eval DATE=strftime(strptime(DATE,"%d%b%Y"),"%Y-%m-%d")
| eval HOUR=if (isnull(HOUR),"0",HOUR) | eval eventHour=substr("0".HOUR,-2,2).":00:00"
| eval _time=strptime(DATE." ".eventHour,"%Y-%m-%d %H:%M:%S")
| table DATE eventHour _time EXPRSSN MIPS

[ITWhisperer]

Is this perhaps just a presentation issue? If all the times on that particular page are 00:00:00, then they are displayed as just the date. If you look at a few pages further on, do you get the correct hours presented? Note that this is particular to the automatic formatting of the _time field.

You could use fieldformat if you want to force the format used

| fieldformat _time=strftime(_time,"%F %T")

[venky1544]

Hi @elomotanpru 

just for curiosity why not concatenate the two fields before using for time format conversion 

| eval _time=strptime(DATE." ".eventHour,"%Y-%m-%d %H:%M:%S") 

is it not strptime would convert it into epoch and then you need to use again strftime to convert the timestamp in the desired format you need 

probably something like below hope it helps 

| makeresults |eval DATE="2021-01-03", eventhour="00:00:00"
|append [| makeresults |eval DATE="2021-01-03", eventhour="02:00:00"]
|append [| makeresults |eval DATE="2021-01-03", eventhour="03:00:00"]
| eval newtime=DATE." ".eventhour
| eval finaltime=strptime(newtime,"%Y-%m-%d %H:%M:%S")
| eval date = strftime(finaltime,"%d/%m/%Y %H:%M:%S")
|fields - finaltime _time newtime

[Marco_Develops]

@elomotanpru  Can you post an example of how you want the time & date to look like? 

[elomotanpru]

Hi,

The desired out put would be something like this:
3/14/2022 09:00:00

[Marco_Develops · Accepted solution]

@elomotanpru  There's many ways to do this, here's one way to do it.

index=* 
| eval DATE = strftime(_time, "%m/%d/%Y")
| eval TIME = strftime(_time, "%T") 
|eval DateAndTime = DATE +" "+TIME 
|table DATE TIME DateAndTime

@elomotanpru The following doc explains Date and Time formatting.

If this helped please like and accept as solution. 

https://docs.splunk.com/Documentation/Splunk/8.2.5/SearchReference/Commontimeformatvariables#Time_va...

[elomotanpru]

Yes it worked for the statistics table version. Would also be able to help me applying this for the time picker version (please see screenshots for reference). I was unable to use the same search query. Did I use the wrong token (timerange) by any chance when using it for earliest and latest?

[Marco_Develops]

@elomotanpru  I was able to get it to work on my system, your Where statement looks strange, what are you trying to accomplish. 

-Marco 

[elomotanpru]

Sorry for the delayed response. When I tried using the same query with the timepicker, the results was that it could not be found but my other query that uses a Text Input was able to return the desired data minus the DateTime format. 

I thought that I needed to add the where clause for it to work because of that result. I check for the dates between February 1 2022 to March 1, 2022.

[venky1544]

hi @elomotanpru 

it should be $timerange.earliest$  you have extra $sign

Hope it helps