topic How to sort a time field in a 12hr time format (AM/PM)? in Splunk Search https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-time-field-in-a-12hr-time-format-AM-PM/m-p/215630#M63202 <P>How do I sort a column of time in 12 hour format with AM / PM on the end? I have tried using eval with the <STRONG>_time</STRONG> field (which gives a standard output like: <CODE>2016-01-13 13:23:38</CODE> and my sourcetype is a standard Windows Security Event Log. </P> <P>The following syntax displays a column called TIME, with the time displayed in 24hr format. I don't need to sort it as it's sorted automatically from earliest to latest.</P> <PRE><CODE>... | eval TIME = strftime(_time, "%H:%M:%S") ... | table TIME </CODE></PRE> <P>However, when changing the time to 12hr format (%I instead of %H) and the trailing AM /PM ( by adding %p), the auto-sort ignores the AM/PM and uses the values as numbers, not 'time-aware' values so to say. </P> <PRE><CODE>... | eval TIME = strftime(_time, "%I:%M:%S %p") ... | table TIME </CODE></PRE> <P>How can the earliest to latest sort be achieved using 12hr time? </P> Tue, 23 Feb 2016 06:33:25 GMT SQservicedesk 2016-02-23T06:33:25Z How to sort a time field in a 12hr time format (AM/PM)? https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-time-field-in-a-12hr-time-format-AM-PM/m-p/215630#M63202 <P>How do I sort a column of time in 12 hour format with AM / PM on the end? I have tried using eval with the <STRONG>_time</STRONG> field (which gives a standard output like: <CODE>2016-01-13 13:23:38</CODE> and my sourcetype is a standard Windows Security Event Log. </P> <P>The following syntax displays a column called TIME, with the time displayed in 24hr format. I don't need to sort it as it's sorted automatically from earliest to latest.</P> <PRE><CODE>... | eval TIME = strftime(_time, "%H:%M:%S") ... | table TIME </CODE></PRE> <P>However, when changing the time to 12hr format (%I instead of %H) and the trailing AM /PM ( by adding %p), the auto-sort ignores the AM/PM and uses the values as numbers, not 'time-aware' values so to say. </P> <PRE><CODE>... | eval TIME = strftime(_time, "%I:%M:%S %p") ... | table TIME </CODE></PRE> <P>How can the earliest to latest sort be achieved using 12hr time? </P> Tue, 23 Feb 2016 06:33:25 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-time-field-in-a-12hr-time-format-AM-PM/m-p/215630#M63202 SQservicedesk 2016-02-23T06:33:25Z Re: How to sort a time field in a 12hr time format (AM/PM)? https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-time-field-in-a-12hr-time-format-AM-PM/m-p/215631#M63203 <P>You can use fieldformat:</P> <PRE><CODE>| fieldformat _time=strftime(_time,"%I:%M:%S %p") </CODE></PRE> Tue, 23 Feb 2016 09:49:11 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-time-field-in-a-12hr-time-format-AM-PM/m-p/215631#M63203 javiergn 2016-02-23T09:49:11Z Re: How to sort a time field in a 12hr time format (AM/PM)? https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-time-field-in-a-12hr-time-format-AM-PM/m-p/215632#M63204 <P>I would suggest to sort first (using TIME) and then change the format.</P> Tue, 23 Feb 2016 18:16:37 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-time-field-in-a-12hr-time-format-AM-PM/m-p/215632#M63204 somesoni2 2016-02-23T18:16:37Z Re: How to sort a time field in a 12hr time format (AM/PM)? https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-time-field-in-a-12hr-time-format-AM-PM/m-p/215633#M63205 <P>Thanks javiergn, but I have tried using fieldformat already. It shows the exact same result as eval - it shows the information, but does not sort it correctly.</P> <P>Example, if I use: </P> <P>| fieldformat _time=strftime(_time,"%I:%M:%S %p")<BR /> | table _time<BR /> | sort _time </P> <P>The results are close, but still not correct: </P> <P>07:57:50 AM<BR /><BR /> 07:58:20 AM<BR /><BR /> 09:52:06 AM<BR /><BR /> 09:52:34 AM<BR /><BR /> 08:09:17 AM<BR /><BR /> 08:09:20 AM<BR /><BR /> 08:09:35 AM<BR /><BR /> 11:42:41 AM<BR /><BR /> 11:43:07 AM<BR /><BR /> 04:02:13 PM</P> Tue, 29 Sep 2020 08:57:49 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-time-field-in-a-12hr-time-format-AM-PM/m-p/215633#M63205 SQservicedesk 2020-09-29T08:57:49Z Re: How to sort a time field in a 12hr time format (AM/PM)? https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-time-field-in-a-12hr-time-format-AM-PM/m-p/215634#M63206 <P>Thanks - I tried this but received the same result.</P> Wed, 24 Feb 2016 02:08:07 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-time-field-in-a-12hr-time-format-AM-PM/m-p/215634#M63206 SQservicedesk 2016-02-24T02:08:07Z