Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Sorting data file by date and time

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sorting data file by date and time
# 1  
Old 07-25-2013
Sorting data file by date and time
Hello.

Sorting data file by date and time with the following issues:
Date is in the following format m/d/yyyy, no leading zeros
Time is in the following format h:m:s AM/PM, no leading zeros

Any ideas on how to sort data when the above issues?
Could the date/time be converted inline to seconds, sorted, and end up in the results with the original format.

I cannot find an easy way to do this with sort, awk ...

Jim
# 2  
Old 07-25-2013
Epoch time may be overkill here, YYYY MM DD HH MM SS 24-hour time will simply sort alphabetically if that can be arranged.

Show a sample of the data and we'll see what we can do.
# 3  
Old 07-25-2013
Sorting data file by date and time
Here is some samples of the field I have to sore by in the data file:
7/25/2013 6:35:34 AM
10/25/2013 6:35:34 AM
7/2/2013 6:35:34 AM
7/25/2013 10:35:34 AM
7/25/2013 6:35:34 PM

There are at least 4 different possibilities, with many combinations
1 and 2 digit month
1 and 2 digit day
1 and 2 digit hour
AM and PM
# 4  
Old 07-25-2013
Censor anything confidential of course, but show me the line! Showing me "a column" isn't enough, I won't know how to get the column I want, I won't know [b]which[/b column, I won't know what separator you use, whether it's always the same column, whether there's issues with variable numbers of columns, etc, etc, etc. I will write code that works for me but not for you, and when it doesn't work for you you won't know why.

Show a whole line. Please.
# 5  
Old 07-25-2013
Here is a complete data line

<c id="xxx01345-383x-4630-b1b9-aaa505939734" t="7/25/2013 6:35:34 AM">
<c id="xxx01345-383x-4630-b1b9-aaa505939734" t="10/25/2013 6:35:34 AM">
<c id="xxx01345-383x-4630-b1b9-aaa505939734" t="7/2/2013 6:35:34 AM">
<c id="xxx01345-383x-4630-b1b9-aaa505939734" t="7/25/2013 10:35:34 AM">
<c id="xxx01345-383x-4630-b1b9-aaa505939734" t="7/25/2013 6:35:34 PM">
# 6  
Old 07-25-2013
Yeah... I'd have never guessed that from what you told me. Smilie

...Are they lines? XML is organized by tags, whether they are lines is purely incidental. This may have as much to do with your sorting problem as anything else.

This will work either way, prepending YYYY/MM/DD:HH:MM:SS times that sort nicely and are easily removed later and printing as individual lines.

Code:
$ cat inputfile

<c id="xxx01345-383x-4630-b1b9-aaa505939734" t="7/25/2013 6:35:34 AM"><c id="xxx01345-383x-4630-b1b9-aaa505939734" t="10/25/2013 6:35:34 AM"><c id="xxx01345-383x-4630-b1b9-aaa505939734" t="7/2/2013 6:35:34 AM"><c id="xxx01345-383x-4630-b1b9-aaa505939734" t="7/25/2013 10:35:34 AM"><c id="xxx01345-383x-4630-b1b9-aaa505939734" t="7/25/2013 6:35:34 PM">

$ awk -v RS="<" -F'"' 'NR>1 {
        split($4, A, " ");
        split(A[1], D, "/");
        split(A[2], T, ":");

        if((A[3] == "PM") && (T[1] != "12")) T[1] += 12;
        if((A[3] == "AM") && (T[1] == "12")) T[1]="00";

        P=sprintf("%04d/%02d/%02d:%02d:%02d:%02d", D[3], D[1], D[2], T[1], T[2], T[3]);

        $0=P" <"$0
        sub(/\n/, "");
        print }' inputfile | sort

2013/07/02:06:35:34 <c id="xxx01345-383x-4630-b1b9-aaa505939734" t="7/2/2013 6:35:34 AM">
2013/07/25:06:35:34 <c id="xxx01345-383x-4630-b1b9-aaa505939734" t="7/25/2013 6:35:34 AM">
2013/07/25:10:35:34 <c id="xxx01345-383x-4630-b1b9-aaa505939734" t="7/25/2013 10:35:34 AM">
2013/07/25:18:35:34 <c id="xxx01345-383x-4630-b1b9-aaa505939734" t="7/25/2013 6:35:34 PM">
2013/10/25:06:35:34 <c id="xxx01345-383x-4630-b1b9-aaa505939734" t="10/25/2013 6:35:34 AM">

$

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Plz Help in sorting the data on date basis

I have file a.txt having below data cat a.txt 01-MAY-13 2.38.11.00.100089 IN 4512 0000741881 01-JUN-13 2.38.11.00.100089 IN 1514 0000764631 01-NOV-13 2.38.11.00.100089 IN 1514 0000856571 01-NOV-13 2.38.15.00.100015 IN 300.32 0000856531 01-JUN-13 2.38.19.00.100000 IN 2698 0000764493... (5 Replies)
Discussion started by: ranabhavish
5 Replies

2. UNIX for Dummies Questions & Answers

Plz Help in sorting the data on date basis

I have file having below data 01-MAY-13 2.38.11.00.100089 IN 4512 0000741881 01-JUN-13 2.38.11.00.100089 IN 1514 0000764631 01-NOV-13 2.38.11.00.100089 IN 1514 0000856571 01-NOV-13 2.38.15.00.100015 IN 300.32 0000856531 01-JUN-13 2.38.19.00.100000 IN 2698 0000764493 01-JUL-13... (2 Replies)
Discussion started by: ranabhavish
2 Replies

3. Shell Programming and Scripting

Sorting the data with date

Hi, PFB the data: C_Random_130417 Java_Random_130518 Perl_Random_120519 Perl_Random_120528 so the values are ending with year,i.e.,130417 i want to sort the values with date. i want the output like this: Perl_Random_120519 Perl_Random_120528 C_Random_130417 Java_Random_130518 can... (5 Replies)
Discussion started by: arindam guha
5 Replies

4. Shell Programming and Scripting

Date and Time sorting

Hi Guys! i have a problem of sorting column chronologically because the data i have in column is in the following format 06/Dec/2006:18:09:54 and need to be sorted in the following way (upto seconds) 06/Dec/2005:18:09:50 06/Dec/2005:18:09:51 31/Mar/2006:19:30:41 24/Oct/2006:19:16:19... (4 Replies)
Discussion started by: me_newbie
4 Replies

5. Shell Programming and Scripting

compare date and time inside data of two files

i have two files with identical no of columns. 6th columns is date (MM/DD/YY format) and 7th columns is time (HH:MM:SS) format. I need to compare these two vaules and if the date & time is higher than fileA, save it on fileC; if the value is lower, then save it on fileD CONDITIONS... (7 Replies)
Discussion started by: ajiwww
7 Replies

6. Shell Programming and Scripting

Split Date/time data to two

Hi all, I have logs(in a log file) with the following structure 20100916011501559;0.812;null;TRUE;;FALSE;0.812;0;0;;19 20100916011504762;0.015;null;TRUE;;FALSE;0;4|4;0.015;;4 20100916011504762;0;null;TRUE;;FALSE;0;0;0;;4 20100916011501731;3.343;null;TRUE;;FALSE;3.156;131|65;0.172;;11... (14 Replies)
Discussion started by: babai
14 Replies

7. UNIX and Linux Applications

Matlab (more generally: sorting data by date)

I'm not sure if this really belongs anywhere on this forum but my previous experiences on here have shown me that you guys are very helpful so I figure I may as well try. I have a bunch of large 2d arrays in matlab and each has a column for a date that each row corresponds to. The format is... (1 Reply)
Discussion started by: skray
1 Replies

8. Shell Programming and Scripting

To extract data of a perticular interval (date-time wise)

I want a shell script which extract data from a log file which contains date and time-wise data and i need the data for a perticular interval of time...what can i do??? (3 Replies)
Discussion started by: abhishek27
3 Replies

9. Shell Programming and Scripting

Processing a log file based on date/time input and the date/time on the log file

Hi, I'm trying to accomplish the following and would like some suggestions or possible bash script examples that may work I have a directory that has a list of log files that's periodically dumped from a script that is crontab that are rotated 4 generations. There will be a time stamp that is... (4 Replies)
Discussion started by: primp
4 Replies

10. UNIX for Dummies Questions & Answers

Sorting by date and time

Hi guys... I've been trying to do this for ages. Maybe you can help. I have log files like the examples below and I have grepped out certain lines from the files so that I can get an idea of who is logging on and how. So now I have the information in a new file but it is now in a different order... (7 Replies)
Discussion started by: padmundo
7 Replies
Login or Register to Ask a Question