Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to Sort 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 How to Sort by Date and Time?
# 8  
Old 10-27-2012
Note that if you want to sort by date and time, with the data you have the following sort command will work:
Code:
sort -k1.7,1 -k1.4,1.5 -k1.1,1.2 -k2,2 input

Note that this sort uses the default whitespace field delimiter, instead of a period because the time stamps aren't delimited by a period. Note also that since your data has fixed-wdith date and time fields with leading zeros, you can use either an alphanumeric sort or a numeric sort for the date fields. Using an alphanumeric sort for the time stamp allows us to use one key-k2,2instead of three keys-k2.1,2.2 -k2.4,2.5 -k2.7,2.8 to sort the time. (A numeric sort-k2n,2would only sort on the hour since ":" is not a numeric character.)
The sort keys (in order) are from the 7th character in the 1st field to the end of the 1st field (year), from the 4th character through the 5th character in the 1st field (month), from the 1st character through the 2nd character in the 1st field (day), and from the start of the 2nd field to the end of the 2nd field (time).

Since your sample input data only had one time stamp per day, I tested the above command using the following input (which duplicates the input you provided but changes the date stamps on the 2nd occurrence of each entry to verify that differences in hours, minutes, and seconds all sort correctly:
Code:
input:
08.05.2012 10:52:32 User: xxxx
15.05.2012 11:30:38 User: xxxx
22.05.2012 11:56:39 User: xxxx
29.05.2012 16:33:04 User: xxxx
19.06.2012 10:51:55 User: xxxx
26.06.2012 11:04:27 User: xxxx
12.06.2012 14:40:27 User: xxxx
05.06.2012 20:46:18 User: xxxx
18.07.2012 03:13:16 User: xxxx
08.05.2012 11:52:32 User: xxxx
15.05.2012 10:30:38 User: xxxx
22.05.2012 11:57:39 User: xxxx
29.05.2012 16:32:04 User: xxxx
19.06.2012 10:50:55 User: xxxx
26.06.2012 11:05:27 User: xxxx
12.06.2012 14:40:28 User: xxxx
05.06.2012 20:46:17 User: xxxx
18.07.2012 01:23:45 User: xxxx

output:
08.05.2012 10:52:32 User: xxxx
08.05.2012 11:52:32 User: xxxx
15.05.2012 10:30:38 User: xxxx
15.05.2012 11:30:38 User: xxxx
22.05.2012 11:56:39 User: xxxx
22.05.2012 11:57:39 User: xxxx
29.05.2012 16:32:04 User: xxxx
29.05.2012 16:33:04 User: xxxx
05.06.2012 20:46:17 User: xxxx
05.06.2012 20:46:18 User: xxxx
12.06.2012 14:40:27 User: xxxx
12.06.2012 14:40:28 User: xxxx
19.06.2012 10:50:55 User: xxxx
19.06.2012 10:51:55 User: xxxx
26.06.2012 11:04:27 User: xxxx
26.06.2012 11:05:27 User: xxxx
18.07.2012 01:23:45 User: xxxx
18.07.2012 03:13:16 User: xxxx

# 9  
Old 10-31-2012
With a slight overhead (about double run time) you could use dateutils dconv tool to normalise the data, sort them, and convert them back:

Code:
dconv -i '%d.%m.%Y %T' -S <<EOF | sort | dconv -f '%d.%m.%Y %T' -S
22.06.2012 17:58:38 CPUser: xxxxxxx, billedAfterStatus: Active
13.07.2012 08:46:15 CPUser: xxxxxxx, billedAfterStatus: Active
20.07.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
20.03.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
20.05.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active

gives you:
Code:
20.03.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
20.05.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
22.06.2012 17:58:38 CPUser: xxxxxxx, billedAfterStatus: Active
13.07.2012 08:46:15 CPUser: xxxxxxx, billedAfterStatus: Active
20.07.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active

It's slightly more robust than pure sort solutions, see Don's comment for instance.
# 10  
Old 10-31-2012
Quote:
Originally Posted by hroptatyr
With a slight overhead (about double run time) you could use dateutils dconv tool to normalise the data, sort them, and convert them back:

Code:
dconv -i '%d.%m.%Y %T' -S <<EOF | sort | dconv -f '%d.%m.%Y %T' -S
22.06.2012 17:58:38 CPUser: xxxxxxx, billedAfterStatus: Active
13.07.2012 08:46:15 CPUser: xxxxxxx, billedAfterStatus: Active
20.07.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
20.03.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
20.05.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active

gives you:
Code:
20.03.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
20.05.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
22.06.2012 17:58:38 CPUser: xxxxxxx, billedAfterStatus: Active
13.07.2012 08:46:15 CPUser: xxxxxxx, billedAfterStatus: Active
20.07.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active

It's slightly more robust than pure sort solutions, see Don's comment for instance.
I guess I don't know what you mean by "more robust".

The sort utility is available on any UNIX system and on any Linux system; the dconv utility is not available on many of those systems. (For example, dconv is not available on OS X.)

The sort command I provided will work for any dates from year 0001 through 9999 as long as all of the dates are in this format. I don't have access to dconv, but I'm guessing that if you add the line:
Code:
20.07.2000 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Historical

to your input file, you'll find that it ends up last on the output rather than first.
(And, yes I know that fixing it for this example only involves adding 3 characters to your command line. Smilie ) However, if you happen to be using a programming environment with a 32-bit time_t, your date range is much more limited.
# 11  
Old 10-31-2012
Hi.

The utility msort is available in many repositories. Here's an example solution using it on Don Cragun's recent demonstration data:
Code:
#!/usr/bin/env bash

# @(#) s1	Demonstrate ordering by date and time fields, msort.
# See: http://freecode.com/projects/msort

# Utility functions: print-as-echo, print-line-with-visual-space, debug.
# export PATH="/usr/local/bin:/usr/bin:/bin"
pe() { for _i;do printf "%s" "$_i";done; printf "\n"; }
pl() { pe;pe "-----" ;pe "$*"; }
db() { ( printf " db, ";for _i;do printf "%s" "$_i";done;printf "\n" ) >&2 ; }
db() { : ; }
C=$HOME/bin/context && [ -f $C ] && $C msort diff

FILE=${1-data1}

pl " Input data file $FILE:"
cat $FILE

pl " Results:"
msort -q --line -n 1,1 -c date -f d.m.y -n 2,2 -c time $FILE |
tee f1

pe
if diff f1 expected-output.txt
then
  pe " Output matches."
else
  pe " Output mis-matches as noted."
fi

exit 0

producing:
Code:
% ./s1

Environment: LC_ALL = C, LANG = C
(Versions displayed with local utility "version")
OS, ker|rel, machine: Linux, 3.0.0-1-amd64, x86_64
Distribution        : Debian GNU/Linux wheezy/sid 
bash GNU bash 4.1.5
msort 8.53
diff (GNU diffutils) 3.2

-----
 Input data file data1:
08.05.2012 10:52:32 User: xxxx
15.05.2012 11:30:38 User: xxxx
22.05.2012 11:56:39 User: xxxx
29.05.2012 16:33:04 User: xxxx
19.06.2012 10:51:55 User: xxxx
26.06.2012 11:04:27 User: xxxx
12.06.2012 14:40:27 User: xxxx
05.06.2012 20:46:18 User: xxxx
18.07.2012 03:13:16 User: xxxx
08.05.2012 11:52:32 User: xxxx
15.05.2012 10:30:38 User: xxxx
22.05.2012 11:57:39 User: xxxx
29.05.2012 16:32:04 User: xxxx
19.06.2012 10:50:55 User: xxxx
26.06.2012 11:05:27 User: xxxx
12.06.2012 14:40:28 User: xxxx
05.06.2012 20:46:17 User: xxxx
18.07.2012 01:23:45 User: xxxx

-----
 Results:
08.05.2012 10:52:32 User: xxxx
08.05.2012 11:52:32 User: xxxx
15.05.2012 10:30:38 User: xxxx
15.05.2012 11:30:38 User: xxxx
22.05.2012 11:56:39 User: xxxx
22.05.2012 11:57:39 User: xxxx
29.05.2012 16:32:04 User: xxxx
29.05.2012 16:33:04 User: xxxx
05.06.2012 20:46:17 User: xxxx
05.06.2012 20:46:18 User: xxxx
12.06.2012 14:40:27 User: xxxx
12.06.2012 14:40:28 User: xxxx
19.06.2012 10:50:55 User: xxxx
19.06.2012 10:51:55 User: xxxx
26.06.2012 11:04:27 User: xxxx
26.06.2012 11:05:27 User: xxxx
18.07.2012 01:23:45 User: xxxx
18.07.2012 03:13:16 User: xxxx

 Output matches.

See URL mentioned in script comments if msort is not in your repository.

Best wishes ... cheers, drl
# 12  
Old 11-01-2012
Quote:
Originally Posted by Don Cragun
I guess I don't know what you mean by "more robust".
I mean that dconv is actually made to parse and operate on dates, sort is not.

Quote:
Originally Posted by Don Cragun
The sort utility is available on any UNIX system and on any Linux system; the dconv utility is not available on many of those systems. (For example, dconv is not available on OS X.)
True.

Quote:
Originally Posted by Don Cragun
The sort command I provided will work for any dates from year 0001 through 9999 as long as all of the dates are in this format. I don't have access to dconv, but I'm guessing that if you add the line:
Code:
20.07.2000 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Historical

to your input file, you'll find that it ends up last on the output rather than first.
(And, yes I know that fixing it for this example only involves adding 3 characters to your command line. Smilie ) However, if you happen to be using a programming environment with a 32-bit time_t, your date range is much more limited.
You guessed wrong. And I'm curious as to what 3 characters you meant? Also, dconv doesn't depend on time_t at all, so it will work exactly the same on 16-bit, 32-bit and 64-bit systems (all tested).

Code:
dconv -S -i '%d.%m.%Y %T' <<EOF | sort | dconv -f '%d.%m.%Y %T' -S
22.06.2012 17:58:38 CPUser: xxxxxxx, billedAfterStatus: Active
13.07.2012 08:46:15 CPUser: xxxxxxx, billedAfterStatus: Active
20.07.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
20.03.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
20.05.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
20.07.2000 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Historical
EOF
  =>
20.07.2000 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Historical
20.03.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
20.05.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
22.06.2012 17:58:38 CPUser: xxxxxxx, billedAfterStatus: Active
13.07.2012 08:46:15 CPUser: xxxxxxx, billedAfterStatus: Active
20.07.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active

# 13  
Old 11-01-2012
Quote:
Originally Posted by hroptatyr
I mean that dconv is actually made to parse and operate on dates, sort is not.
OK. I'll take your word for it. My system doesn't have dconv and there are no dconv man pages in the sets of man pages provided here on unix.com.
Quote:
You guessed wrong. And I'm curious as to what 3 characters you meant? Also, dconv doesn't depend on time_t at all, so it will work exactly the same on 16-bit, 32-bit and 64-bit systems (all tested).

Code:
dconv -S -i '%d.%m.%Y %T' <<EOF | sort | dconv -f '%d.%m.%Y %T' -S
22.06.2012 17:58:38 CPUser: xxxxxxx, billedAfterStatus: Active
13.07.2012 08:46:15 CPUser: xxxxxxx, billedAfterStatus: Active
20.07.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
20.03.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
20.05.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
20.07.2000 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Historical
EOF
  =>
20.07.2000 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Historical
20.03.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
20.05.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active
22.06.2012 17:58:38 CPUser: xxxxxxx, billedAfterStatus: Active
13.07.2012 08:46:15 CPUser: xxxxxxx, billedAfterStatus: Active
20.07.2012 08:56:24 CPUser: xxxxxxx, billedAfterStatus: Active

Without access to a dconv man page I (apparently incorrectly) assumed that dconv -S -i '%d.%m.%Y %T' used the strptime() format string argument to convert the text date string into a seconds since the Epoch value, sort sorted the seconds since the Epoch values, and dconv -f '%d.%m.%Y %T' -S converted seconds since the Epoch back to the original text string format using an strftme() date format string. Since the number of seconds since the Epoch in 2012 is represented as a ten digit decimal string in the range 1325xxxxxx-1357xxxxxx and the number of seconds since the Epoch in 2000 was a nine digit string in the range 946xxxxxx-978xxxxxx, I expected the alphanumeric sort specified by sort to sort "9" after "1". If my assumption had been correct, it could have been fixed by telling sort to do a numeric sort sort -n instead of an alphanumeric sort sort.
# 14  
Old 11-01-2012
Quote:
Originally Posted by Don Cragun
OK. I'll take your word for it. My system doesn't have dconv and there are no dconv man pages in the sets of man pages provided here on unix.com.

Without access to a dconv man page I (apparently incorrectly) assumed that dconv -S -i '%d.%m.%Y %T' used the strptime() format string argument to convert the text date string into a seconds since the Epoch value, sort sorted the seconds since the Epoch values, and dconv -f '%d.%m.%Y %T' -S converted seconds since the Epoch back to the original text string format using an strftme() date format string. Since the number of seconds since the Epoch in 2012 is represented as a ten digit decimal string in the range 1325xxxxxx-1357xxxxxx and the number of seconds since the Epoch in 2000 was a nine digit string in the range 946xxxxxx-978xxxxxx, I expected the alphanumeric sort specified by sort to sort "9" after "1". If my assumption had been correct, it could have been fixed by telling sort to do a numeric sort sort -n instead of an alphanumeric sort sort.
Ah I see. No, what happens is dconv with no output format specifiers will return ISO 8601 dates (aka 2012-10-31T17:44:00) which are sortable just like that, UNLESS of course you go before the year 1000 or after the year 9999, but ISO isn't defined for those dates anyway Smilie.
This User Gave Thanks to hroptatyr For This Post:
Don Cragun
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Sort date time in ascending order

Hi, i had a data block (coming from pipe from other codes) as: H YF_CO.dat 77164 11/17/2013 04:00:02 731374590.96 1 1 731374590.96 76586 77164 578 2988 Y H YF_CO.dat 77164 11/17/2013 04:00:07 731374590.96 1 4 731374590.96 76586 77164 578 2988 Y H YF_CO.dat 77178 ... (5 Replies)
Discussion started by: pr5439
5 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

Displaying current date time of EDT in IST time

Hi Folks, My server time is in EDT. And i am sending automated mails from that server in which i need to display the current date time as per IST (GMT+5:30). Please advice how to display the date time as per IST. IST time leads 9:30 mins to EDT. and i wrote something like below. ... (6 Replies)
Discussion started by: Showdown
6 Replies

4. Shell Programming and Scripting

Help with sort word and general numeric sort at the same time

Input file: 100%ABC2 3.44E-12 USA A2M%H02579 0E0 UK 100%ABC2 5.34E-8 UK 100%ABC2 3.25E-12 USA A2M%H02579 5E-45 UK Output file: 100%ABC2 3.44E-12 USA 100%ABC2 3.25E-12 USA 100%ABC2 5.34E-8 UK A2M%H02579 0E0 UK A2M%H02579 5E-45 UK Code try: sort -k1,1 -g -k2 -r input.txt... (2 Replies)
Discussion started by: perl_beginner
2 Replies

5. Shell Programming and Scripting

Adding time to date time in UNIX shell scipting

I needed some help in adding a duration (in seconds) to a start time (in hhmmss format) and a start date (in mmddyy format) in order to get an end date and end time. The concept of a leap year is also to be considered while incrementing the day. The code/ function that I have formed so far is as... (3 Replies)
Discussion started by: codehelp04
3 Replies

6. Solaris

modifying date and time and time zone on solaris 5.10 with (redundant server) veritas

I have a cluster of two Solaris server (veritas cluster). one working and the other is standby I am going to change the date on them , and am looking for a secure solution as it is giving an important service. my opinion is that the active one doesn't need to be restarted (if I don't change the... (1 Reply)
Discussion started by: barry1946
1 Replies

7. UNIX for Dummies Questions & Answers

Converting string date time to unix time in AWK

I'd like to convert a date string in the form of sun aug 19 09:03:10 EDT 2012, to unixtime timestamp using awk. I tried This is how each line of the file looks like, different date and time in this format Sun Aug 19 08:33:45 EDT 2012, user1(108.6.217.236) all: test on the 17th ... (2 Replies)
Discussion started by: bkkid
2 Replies

8. Shell Programming and Scripting

shell script to sort entries in a file by date and time

Hello All, Need a shell script to sort entries in a file by date and time. Below are the entries in the file, i need to sort it first by the date and then time Note :- Date is in MM/DD/YY format and date comes as the 6th & time comes on 7th coloumns respectively. 150 pbnawldb001-b... (10 Replies)
Discussion started by: ajiwww
10 Replies

9. Shell Programming and Scripting

Convert Epoch Time to Standard Date and Time & Vice Versa

Hi guys, I know that this topic has been discuss numerous times, and I have search the net and this forum for it. However, non able to address the problem I faced so far. I am on Solaris Platform and unable to install additional packages like the GNU date and gawk to make use of their... (5 Replies)
Discussion started by: DrivesMeCrazy
5 Replies

10. Shell Programming and Scripting

Sort By Date and Time

Hi , I would like to list or sort by date and time (the files are named in day and time format) where the latest file will be placed at the bottom and the earliest file be placed at the top. Can anybody help me? My files are named in the following manner. EG: abc_071128_144121_data "... (21 Replies)
Discussion started by: Raynon
21 Replies
Login or Register to Ask a Question