Sort by date field in AIX


 
Thread Tools Search this Thread
Operating Systems AIX Sort by date field in AIX
# 1  
Old 08-04-2015
Sort by date field in AIX

I wanted to sort the below data on 4th field(comma seperator) based on month and date and time on AIX OS.

Input data:
Code:
3,AJ,30 Jul 06:30,30 Jul 06:30
5,AJ,30 Jul 06:30,30 Jul 06:49
10,AJ,30 Jul 06:30,02 Jan 05:41
4,AJ,30 Jul 06:30,30 Jul 06:36
2,AJ,30 Jul 06:30,28 Jul 06:45
9,AJ,30 Jul 06:30,04 Aug 05:41
1,AJ,30 Jul 06:30,04 Aug 06:25
1,AJ,30 Jul 06:30,28 Jul 06:39
6,AJ,30 Jul 06:30,04 Jun 06:55
7,AJ,30 Jul 06:30,30 Jul 06:55
8,AJ,30 Jul 06:30,30 Jul 08:54
9,AJ,30 Jul 06:30,04 Jun 05:41

Expected Output:
Code:
10,AJ,30 Jul 06:30,02 Jan 05:41
9,AJ,30 Jul 06:30,04 Jun 05:41
6,AJ,30 Jul 06:30,04 Jun 06:55
1,AJ,30 Jul 06:30,28 Jul 06:39
2,AJ,30 Jul 06:30,28 Jul 06:45
3,AJ,30 Jul 06:30,30 Jul 06:30
4,AJ,30 Jul 06:30,30 Jul 06:36
5,AJ,30 Jul 06:30,30 Jul 06:49
7,AJ,30 Jul 06:30,30 Jul 06:55
8,AJ,30 Jul 06:30,30 Jul 08:54
9,AJ,30 Jul 06:30,04 Aug 05:41
1,AJ,30 Jul 06:30,04 Aug 06:25

---------- Post updated at 02:40 PM ---------- Previous update was at 01:25 PM ----------

In unix I am able to do above with below code but I am not able to do same in AIX as M option is not there so can anyone suggest how to do ?

Code:
sort -t, -k4.3M -k4 -k4.8,4.12 file_name

# 2  
Old 08-04-2015
I'm afraid you need to convert the file to numeric months, then, sort it, and convert back. Try
Code:
awk '
BEGIN           {for (i=split("Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec", T); i>0; i--) NM[T[i]]=i
                }
                {$2=sprintf ("%02d", NM[$2])
                 $4=sprintf ("%02d", NM[$4])
                }
1
' file |
sort -t, -k4.3n -k4 -k4.8,4.12 |
awk '
BEGIN           {split("Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec", NM)
                }
                {$2=sprintf ("%s", NM[$2+0])
                 $4=sprintf ("%s", NM[$4+0])
                }
1

Not sure this is the most elegant/efficient solution, though.

Last edited by RudiC; 08-04-2015 at 08:01 AM..
# 3  
Old 08-05-2015
You could also try downlonding aixtools.gnu.coreutils - info available at coreutils - AIXTOOLS

You will also need aixtools.gettext or aixtools.gnu.gettext (via gettext - AIXTOOLS)

These are installp packages, and I have been assuming that people know how to work with installp. That assumption seems to be wrong, based on some feedback I have gotten the last two weeks.

Note: aixtools.gnu.coreutils is the latest version I have packaged. Unfortunately, it is not installing as smoothly as I would like. In that case install the 8.21 version (I have been using that for years).

The "gettext" fileset is also needed - as gnu uses gettext for language support.

FYI: gnu sort is part of coreutils.

Please keep me posted of any issues you have. It may take 24 hours before I have time to look into it, but I shall "correct" asap.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Linux

How do I format a Date field of a .CSV file with multiple commas in a string field?

I have a .CSV file (file.csv) whose data are all enclosed in double quotes. Sample format of the file is as below: column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10 "12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in... (3 Replies)
Discussion started by: dhruuv369
3 Replies

2. Shell Programming and Scripting

Sort help: How to sort collected 'file list' by date stamp :

Hi Experts, I have a filelist collected from another server , now want to sort the output using date/time stamp filed. - Filed 6, 7,8 are showing the date/time/stamp. Here is the input: #---------------------------------------------------------------------- -rw------- 1 root ... (3 Replies)
Discussion started by: rveri
3 Replies

3. Shell Programming and Scripting

Sort field and uniq

I have a flatfile A.txt 2012/12/04 14:06:07 |trees|Boards 2, 3|denver|mekong|mekong12 2012/12/04 17:07:22 |trees|Boards 2, 3|denver|mekong|mekong12 2012/12/04 17:13:27 |trees|Boards 2, 3|denver|mekong|mekong12 2012/12/04 14:07:39 |rain|Boards 1|tampa|merced|merced11 How do i sort and get... (3 Replies)
Discussion started by: sabercats
3 Replies

4. Shell Programming and Scripting

Sorting Date Field with Sort -k :/

SOLVED : (17 Replies)
Discussion started by: Glitch100
17 Replies

5. UNIX for Advanced & Expert Users

Sort on basis of particular field

05.50.25:AIRE.S:RESTRICTED:S2: 05.50.25:ANDR.VI:RESTRICTED:S2: 05.50.25:BASF.MI:RESTRICTED:N: 05.50.25:BMWG.DE:RESTRICTED:N: 05.50.25:BORE.ST:RESTRICTED:N: I can sort of the basis of second field using " sort -t: -k2,2 " but what i want to sort the data is on the basis its extension e.g.:... (9 Replies)
Discussion started by: manishma71
9 Replies

6. UNIX for Dummies Questions & Answers

Sort field by date mm/dd/yyyy

Hello Group, I would like to sort the below file by date (first year then month and day) and I used the following command but it does not work sort -n -t"/" -k3 -k1 -k2 "sample original file" 12/28/2009,1.0353 12/31/2009,1.0357 12/30/2009,1.0364 12/29/2009,1.0366 12/24/2009,1.0386... (6 Replies)
Discussion started by: csierra
6 Replies

7. Shell Programming and Scripting

Sort two columns in a field, one of them being a date

Hi, I have a set of columns in a csv file, my first row being an integer and 2nd being a date. I want to first sort it using the first column and then by the second. for e.g. i have , 1234,09/05/2009,hi 5678,01/01/2008,hi 1234,11/03/2006,hello 5678,28/07/2010,hello i tried this... (5 Replies)
Discussion started by: sweta_doshi
5 Replies

8. Shell Programming and Scripting

Sort alpha on 1st field, numerical on 2nd field (sci notation)

I want to sort alphabetically on the first field and sort in descending numerical order on the 2nd field. With a normal "sort -r -n" it does this: abc ||| 5e-05 ||| bla abc ||| 3 ||| ble def ||| 1 ||| abc def ||| 0.2 ||| def As you can see it ignores the fact that 5e-05 is actually 0.00005... (1 Reply)
Discussion started by: FrancoisCN
1 Replies

9. Shell Programming and Scripting

How to Sort files on date field

:cool: Hi all, I have a pecular issue in sorting these files in Solaris environment. All the below files are modified on November 4th, but I want to sort these files as per date column (eg: 01May07_1623 = ddmmmyy_hhmm) Nov 4 18:27 SONYELEC00.GI22973.01May07_1623.gpg Nov 4 18:27... (4 Replies)
Discussion started by: shivaastrogun
4 Replies

10. Shell Programming and Scripting

How to sort a field in a file having date values

Hi All, I am having a pipe delimited file .In this file the 3rd column is having date values.I need to get the min date and max date from that file. I have used cut -d '|' test.dat -f 3|sort -u But it is not sorting the date .How to sort the date column using unix commands Thanks ... (4 Replies)
Discussion started by: risshanth
4 Replies
Login or Register to Ask a Question