CODEWITHSUNDEEP
perlbashshellsedawk
Debaditya
Debaditya

Reputation: 2497

Sort Date/Time in Unix

Input 

2012-07-24 10:05:08 AM
2012-07-26 10:13:58 AM
2012-07-24 10:13:58 AM
2012-07-24 10:57:50 AM
2012-07-24 11:15:03 AM
2012-07-24 11:26:08 PM
2012-07-25 11:26:08 PM

Desired Output

2012-07-24 10:05:08 AM
2012-07-24 10:13:58 AM
2012-07-24 10:57:50 AM
2012-07-24 11:15:03 AM
2012-07-24 11:26:08 PM
2012-07-25 11:26:08 PM
2012-07-26 10:13:58 AM

Code I tried 

 sort -t ":" -k 1 -k 2 -k 3 Input.txt | sort -t " " -k 3

But I am not getting desired output.

Can anyone suggest anything?

I wrote a code... but still problem persists...

Code

 sed 's/ 12:/00:/g' Input.txt | sort -k 1,1 -k 3,3 -k 2,2 | sed 's/00:/12:/g'

First change 12:43:01 AM to 00:43:01 AM....and then apply sort command.

Upvotes: 4

Views: 2446

Answers (9)

Debaditya
Debaditya

Reputation: 2497

Finally I coded without using any external modules. Though its lengthy but working smoothly for any date format.

Technique Used :

  1. First covert each date in the file to its timestamp
  2. Sort the timestamp as they are in numbers.
  3. Again convert the timestamp into date using scalar localtime.

Code

my @input = `cat Input.txt`;

    open (ts,">","tt.txt");
    foreach my $i (@input)
    {
            chomp($i);
            my $timestamp = `date --date "$i" +\%s`;
            chomp($timestamp);
            push (@time,$timestamp);
            print ts "$timestamp\n";
    }
    close(ts);

    open (ts,">","sort_time.txt");
    my @sorted_time = join "\n",sort {$a<=>$b} @time;
    chomp(@sorted_time);
    print ts "@sorted_time\n";
    close(ts);

    my @input1=  `cat sort_time.txt`;
    open (ts,">","sort_timestamp.txt");
    foreach my $st1 (@input1)
    {
            chomp($st1);
            my $st2 = scalar localtime($st1);
            chomp($st2);
            print ts "$st2\n";
    }
    close(ts);



    @input2 = `cat sort_timestamp.txt`;
    open (ts,">","Output.txt");
    foreach my $st2 (@input2)
    {
            chomp($st2);
            $pro_time = `date --date "$st2" +\%Y-\%m-\%d~\%r | sed 's/~/ /g'`;
            chomp($pro_time);
            print ts "$pro_time\n";
    }
    close(ts);


    `rm tt.txt sort_time.txt sort_timestamp.txt`;

Upvotes: -1

Hynek -Pichi- Vychodil
Hynek -Pichi- Vychodil

Reputation: 26121

You can use this:

sed 's/ 12:/ 00:/'| LC_ALL="C" sort -k 1,1 -k 3 | sed 's/ 00:/ 12:/'

It should be pretty fast solution.

Upvotes: 1

potong
potong

Reputation: 58473

This might work for you (GNU sed):

sed 's/.*/echo -e "$(date -d"&" +%s)\t&"/e' file | sort -n | sed 's/.*\t//'

or:

date -f file +%s | paste - file | sort -n | sed 's/\S\+\s\+//'

Upvotes: 0

Todd A. Jacobs
Todd A. Jacobs

Reputation: 84393

Convert to Epoch Seconds for Sorting

Assuming that your data is stored in /tmp/foo, you can convert the timestamp into a numerically-sortable format with GNU date. For example:

date -f /tmp/foo '+%s' | sort |
while read; do
    date -d "@$REPLY" "+%F %I:%M:%S %p"
done

This should correctly handle the sort in all cases, and especially the cases where all AM times should come before all PM times on the same date. For example, 12:01 AM is now listed before 10:00 PM.

Upvotes: 4

Toto
Toto

Reputation: 91488

Using Schartzian Transform and Date::Parse :

use strict;
use warnings;
use 5.010;
use Date::Parse;

my @data = <DATA>;
chomp @data;

my @sorted = 
    map  { $_->[0] }
    sort { $a->[1] <=> $b->[1] }
    map  { [$_, str2time($_)] } @data;

say for @sorted;

__DATA__
2012-07-24 11:15:03 AM
2012-07-24 11:26:08 PM
2012-07-25 01:26:08 PM
2012-07-25 12:26:08 PM
2012-07-25 01:26:08 AM
2012-07-25 12:26:08 AM
2012-07-25 11:26:08 AM
2012-07-25 11:26:08 PM

output:

2012-07-24 11:15:03 AM
2012-07-24 11:26:08 PM
2012-07-25 12:26:08 AM
2012-07-25 01:26:08 AM
2012-07-25 11:26:08 AM
2012-07-25 12:26:08 PM
2012-07-25 01:26:08 PM
2012-07-25 11:26:08 PM

Upvotes: 2

Borodin
Borodin

Reputation: 126742

The strings could simply be sorted lexically except for the 12-hour times.

This solution uses the Schwartzian Transform to change the key that is used to sort the strings. It just adds twelve to the hour field of any string ending with PM and sorts by that instead.

use strict;
use warnings;

my @data = <DATA>;
chomp @data;

my @sorted = map $_->[0],
sort { $a->[1] cmp $b->[1] }
map { (my $dt = $_) =~ s/(\d\d)(?=:\d\d:\d\d PM)/$1+12/e; [$_, $dt] } @data;

print "$_\n" for @sorted;


__DATA__
2012-07-24 10:05:08 AM
2012-07-26 10:13:58 AM
2012-07-24 10:13:58 AM
2012-07-24 10:57:50 AM
2012-07-24 11:15:03 AM
2012-07-24 11:26:08 PM
2012-07-25 11:26:08 PM

output

2012-07-24 10:05:08 AM
2012-07-24 10:13:58 AM
2012-07-24 10:57:50 AM
2012-07-24 11:15:03 AM
2012-07-24 11:26:08 PM
2012-07-25 11:26:08 PM
2012-07-26 10:13:58 AM

Update

As steffen has pointed out, even after adjusting the hours for am/pm, midnight and midday still prevent a simple string sort from working.

This program uses the core Time::Piece module to reformat the date/times in ISO 8601 format 2000-02-29T12:34:56 which can be sorted lexically.

use strict;
use warnings;

use Time::Piece;

my @data = <DATA>;
chomp @data;

my @sorted = map $_->[0],
sort { $a->[1] cmp $b->[1] }
map { [ $_, toISO8601($_) ] } @data;

sub toISO8601 {
  Time::Piece->strptime(@_, '%Y-%m-%d %I:%M:%S %p')->datetime;
}

print "$_\n" for @sorted;

__DATA__
2012-07-24 10:05:08 AM
2012-07-26 10:13:58 AM
2012-07-24 10:13:58 AM
2012-07-24 10:57:50 AM
2012-07-24 11:15:03 AM
2012-07-24 11:26:08 PM
2012-07-25 11:26:08 PM
2012-08-01 01:00:00 PM
2012-08-01 12:30:00 PM
2012-08-01 12:00:00 PM
2012-08-01 11:30:00 AM
2012-08-01 01:00:00 AM
2012-08-01 12:30:00 AM
2012-08-01 12:00:00 AM

output

2012-07-24 10:05:08 AM
2012-07-24 10:13:58 AM
2012-07-24 10:57:50 AM
2012-07-24 11:15:03 AM
2012-07-24 11:26:08 PM
2012-07-25 11:26:08 PM
2012-07-26 10:13:58 AM
2012-08-01 12:00:00 AM
2012-08-01 12:30:00 AM
2012-08-01 01:00:00 AM
2012-08-01 11:30:00 AM
2012-08-01 12:00:00 PM
2012-08-01 12:30:00 PM
2012-08-01 01:00:00 PM

Upvotes: 3

Slava Semushin
Slava Semushin

Reputation: 15214

There is my variant:

$sed 's|\([0-9]\+\)-\([0-9]\+\)-\([0-9]\+\) \([0-9]\+\):\([0-9]\+\):\([0-9]\+\) \([A-Z]\+\)|\1 \2 \3 \4 \5 \6 \7 \0|' input.txt | awk '{if($7=="AM"){$7="1";if($4==12){$4 = 0}}else{$7="0"};print}' | sort -n -k1 -k2 -k3 -k4 -k5 -k6 -k7 | cut -d' ' -f 8-
2012-07-24 10:05:08 PM
2012-07-24 10:13:58 AM
2012-07-24 10:57:50 AM
2012-07-24 11:15:03 AM
2012-07-24 11:26:08 PM
2012-07-25 11:26:08 PM
2012-07-26 10:13:58 AM

The main idea to add extra fields, sort by them and after sorted I get rid of them. Sorting numerics is simple, but to sort AM/PM I convert it to 1/0 digits to simplify sorting.

Updated: sed + awk usage may be replaced by awk:

awk -F'[-: ]' '{printf("%d %d %d %d %d %d %d %s\n", $1, $2, $3, ($4 == 12 && $7 == "AM" ? 0 : $4), $5, $6, $7 == "AM", $0)}' input.txt |
sort -n -k1 -k2 -k3 -k4 -k5 -k6 -k7 |
cut -d' ' -f 8-

Updated: fix AM/PM issue

Upvotes: 0

steffen
steffen

Reputation: 8968

a little bit awkward, I admit...

cat Input.txt | \ awk 'BEGIN{FS="[: -]"}{if($7 == "PM") $4+=12; print $1"-"$2"-"$3" "$4":"$5":"$6" "$7}'|\ sort|\ awk 'BEGIN{FS="[: -]"}{if($7 == "PM") $4-=12; print $1"-"$2"-"$3" "$4":"$5":"$6" "$7}'

edit:

cat Input.txt |\
awk 'BEGIN{FS="[: -]"}{if(length($4)==1) $4="0"$4 ;if($7 == "PM") $4+=12; else if($4 ==12)$4-=12; print $1"-"$2"-"$3" "$4":"$5":"$6" "$7}'|\
sort|\
awk 'BEGIN{FS="[: -]"}{if($7 == "PM") $4-=12; else if($4 ==0)$4+=12; print $1"-"$2"-"$3" "$4":"$5":"$6" "$7}'

But it works...

explanation: I convert the time format to 24 hours using awk, sort it and convert it back.

edit: I prepend a 0 to hours with only one digit in order to get 1:0:0 and 12:0:0 sorted right. Also for AM.

Upvotes: 2

robinr
robinr

Reputation: 4456

12:01 AM comes before 1:01 AM, so I can't see how using just sort will help.

You need to convert to some other format, e.g. ISO 8601 or seconds since whenever to get something that can be compared as text or numbers. A perl oneliner will do that.

Upvotes: 1

Related Questions