Trying to get the correct format for start and end time

[chinmay25]

I am working with the following query....however, the start time and end tied output that i am getting is below and some of the times is listed several times.

06/22/2020 15:24:06.370000

I am trying to get only the time instead of the current format. 

index= XYZ  SMF30JBN=M*DDD* SMF30JNM=JOB* (SMF30STP=1 OR SMF30STP=5) sourcetype="syncsort:smf030"
| rename SMF30JNM as JOBNUMBER SMF30JBN as JOBNAME
| eval START = case(SMF30STP=1,strptime(DATETIME, "%Y-%m-%d %H:%M:%S.%2N"))
| eval END = case(SMF30STP=5,strptime(DATETIME, "%Y-%m-%d %H:%M:%S.%2N"))
| stats values(START) as START values(END) as END by JOBNUMBER JOBNAME
| convert dur2sec(START) as STARTTIME dur2sec(END) as ENDTIME
| convert ctime(STARTTIME) as START_TIME ctime(ENDTIME) as END_TIME
| table JOBNAME START_TIME END_TIME

[anilchaithu · Accepted solution]

@chinmay25 

one solution would be using the below command instead of convert

| eval START_TIME=strftime(START , "%H:%M:%S),  END_TIME=strftime(END , "%H:%M:%S)

| table JOBNAME START_TIME END_TIME

The multiple values are due to the below command

| stats values(START) as START values(END) as END by JOBNUMBER JOBNAME

[chinmay25]

Hi Anil,

Thank you for the help. It gave the desired output for the time format.

However, if i remove the stats command to remove the duplicates of time... most of my data is missing with either start time or end time.

start time	end time
6:55:53
6:55:33
	6:55:32
6:54:49
	6:54:48

Where as if i keep the stats command I see the result where some of the time is showing multiple events.

ex. 

8:40:42

8:40:42

[anilchaithu]

@chinmay25 

you can use mvdedup after stats to remove duplicate values.

eval START_TIME=mvdedup(START_TIME), END_TIME=mvdedup(END_TIME)

also you can use list in place of values

| stats list(START) as START list(END) as END by JOBNUMBER JOBNAME

If this helps, upvote would be appreciated

[chinmay25]

Hi Anil,

The latest suggestion didnt help. But thanks for the earlier helped and I will vote.

Chinmay.