Solaris + sort file according to date and time by sort command

Question

I have along list that created in /var/tmp/file.txt from some script (in Solaris machine) the following list have 4 fields

please advice how to sort the list according to the following TIMESTAMP ( by sort command or other solaris command )

for example the date & time 15-10-2009 08:29:18 should be before 15-10-2009 08:29:10 ... etc

example of file.txt ( not sorted file )

 PHONE_NUMBER TIMESTAMP ID TYPE -------------------- ------------------- ---- -------------- 972544111222 15-10-2009 08:29:18 20 sharp_gx10 33633333333 24-09-2009 16:17:45 20 other_mm_phone 841990000043 08-10-2009 09:04:38 60 other_mm_phone 972541230001 08-10-2009 14:23:48 20 other_mm_phone 

. . . .

I try the sort command but not clearly why file.txt not sorted

sort -t' ' -k2.7,2.10n -k2.4,2.5n -k2.1,2.2n -k3 /var/tmp/file.txt 

we get the output:

 5938123456789141 12-10-2009 13:09:22 20 other_phone 5511223322332233 07-03-2012 08:13:43 20 other_phone 888888 10-02-2012 14:13:58 60 LegacyPhone 111111 10-02-2012 14:13:59 60 LegacyPhone 777777 10-02-2012 14:13:59 60 LegacyPhone 999999 16-02-2012 14:07:32 10 other_phone 87654321 11-10-2009 09:39:37 10 other_phone 

Answer 1

sort -t' ' -k2.7,2.10n -k2.4,2.5n -k2.1,2.2n -k3 

If you have control of the original script so you can print the date as YYYY-MM-DD then it would sort naturally so we would not need to pick apart the year, month and date fields in the sort command, but could just use -k2 in the same way we just use -k3 for the timestamp.

Or if I have counted your fixed width format correctly,

sort -k1.28,1.31n -k1.25,1.26n -k1.22,1.23n -k3 

Or if you are not using fixed widths and have variable white space, then (-b to ignore spaces but note no -n)

sort -b -k2.7,2.10 -k2.4,2.5 -k2.1,2.2 -k3 

Or to sort only part of the file (and really it would be a lot easier simply to alter the program creating this data in the first place):

(head -2 file; /usr/xpg4/bin/tail -n +3 file |sort -b -k2.7,2.10 -k2.4,2.5 -k2.1,2.2 -k3) 

Answer 2

With complex cases like this I tend to create a temporary MySQL database and let that do the sorting or other filtering. Often it's easier to just do something like

CREATE TABLE temp_sort_table(phone_number VARCHAR(16), timestamp DATETIME, id INT, type VARCHAR(16)) 

or so and then just import the data from the text file to your database:

LOAD DATA INFILE 'file.txt' INTO TABLE temp_sort_table 

Then you can easily do things like

SELECT * FROM temp_sort_table ORDER BY timestamp DESC; 

Sure, you can pass all kinds of parameters to sort command or apply some awk/Perl/Python voodoo to get all this done without a database. With complex text files it's just easier to use a database, in my opinion.