[SOLVED] Sort date time field based on AM/PM via shell script

Issue

I am trying to sort these two lines reversely based on date and time, i am using the below command, and it is working fine but not considering AM and PM, so the below two line not sorted correctly as the time of first one is 10 AM while the second one is 2 PM

command:

sort -t',' -k2,2 -k7,7r file.csv

lines:

before sorting:

field1,field2,field3,field4,field5,field6,19-NOV-23 10.58.03.000000 AM
field1,field2,field3,field4,field5,field6,19-NOV-23 02.48.47.000000 PM

lines after sorting (same arrangement)

field1,field2,field3,field4,field5,field6,19-NOV-23 10.58.03.000000 AM
field1,field2,field3,field4,field5,field6,19-NOV-23 02.48.47.000000 PM

Solution

man sort (GNU sort, that is):

-k, --key=KEYDEF
       sort via a key; KEYDEF gives location and type

KEYDEF  is F[.C][OPTS][,F[.C][OPTS]] for start and stop position, where
F is a field number and C a character position in the field;  both  are
origin 1, and the stop position defaults to the line's end. - -  
OPTS is one or more single-letter ordering options [bdfgiMhnRrV] - -

Field 7 in your data:

         1111111111222222222
1234567890123456789012345678
19-NOV-23 10.58.03.000000 AM
19-NOV-23 02.48.47.000000 PM

First define reverse (or in the OPTS), field separator and the year (field 7, positions 8 and 9, numeric):

sort -r -t, -k7.8,7.9n file

Then the month (M ordering option in GNU sort):

sort -r -t, -k7.8,7.9n -k7.4,7.6M file

Then the day and AM/PM (actually A/P):

sort -r -t, -k7.8,7.9n -k7.4,7.6M -k7.1n,7.2n -k7.27 file

And then the time part (left for exercise).

With above you get:

field1,field2,field3,field4,field5,field6,19-NOV-23 02.48.47.000000 PM
field1,field2,field3,field4,field5,field6,19-NOV-23 10.58.03.000000 AM

Answered By - James Brown

Answer Checked By - Robin (WPSolving Admin)