1

I have a log file with contents like this:

11-12-2014 - 03:03:59AM lat = 41.990516; lon = -93.430704<br>
11-12-2014 - 03:05:15AM lat = 41.001546; lon = -93.443352<br>
11-12-2014 - 03:11:50AM lat = 42.039054; lon = -93.442001<br>
11-12-2014 - 12:08:03AM lat = 41.937911; lon = -93.369249<br>
11-12-2014 - 12:11:29AM lat = 41.949656; lon = -93.329133<br>
11-12-2014 - 12:23:02AM lat = 42.025385; lon = -93.347026<br>
11-12-2014 - 12:29:10AM lat = 41.033341; lon = -93.380586<br>
11-12-2014 - 12:38:08AM lat = 41.036720; lon = -93.436851<br>
11-12-2014 - 12:45:20AM lat = 41.998129; lon = -93.400943<br>
11-12-2014 - 12:53:36AM lat = 41.961489; lon = -93.414624<br>

How can I convert this to 24-hour time and sort it correctly?

  • Is that date November 12th or December 11th? – Anthon Nov 12 '14 at 16:33
  • November 12. It is formatted as MM-DD-YYYY - 12HourTime – Robert Altman Nov 12 '14 at 17:05
0

With perl:

$ perl -MTime::Piece -anle '
  $F[2] = Time::Piece->strptime($F[2],"%r")->strftime("%H:%M:%S");
  push @out, [$F[2]."-".join("-", reverse(split("-",$F[0]))), join(" ",@F)];
  END {
    print for map  { $_->[1] }
              sort { $a->[0] cmp $b->[0] } @out;
}' file
11-12-2014 - 00:08:03 lat = 41.937911; lon = -93.369249<br>
11-12-2014 - 00:11:29 lat = 41.949656; lon = -93.329133<br>
11-12-2014 - 00:23:02 lat = 42.025385; lon = -93.347026<br>
11-12-2014 - 00:29:10 lat = 41.033341; lon = -93.380586<br>
11-12-2014 - 00:38:08 lat = 41.036720; lon = -93.436851<br>
11-12-2014 - 00:45:20 lat = 41.998129; lon = -93.400943<br>
11-12-2014 - 00:53:36 lat = 41.961489; lon = -93.414624<br>
11-12-2014 - 03:03:59 lat = 41.990516; lon = -93.430704<br>
11-12-2014 - 03:05:15 lat = 41.001546; lon = -93.443352<br>
11-12-2014 - 03:11:50 lat = 42.039054; lon = -93.442001<br>
| improve this answer | |
0

You can do this using the GNU date command. It can take strings and print out the corresponding date:

$ date -d "11/11/2014 04:12:03PM"
Tue Nov 11 16:12:03 CET 2014

Note, however, that it does not like DD-MM-YYY:

$ date -d "11-11-2014"
date: invalid date ‘11-11-2014’

So, first run a sed on your file to replace all - with /. Then, pass that through read to get each field into a separate variable, convert and sort:

$ sed 's#-#/#g' file | while read date _ hour rest; do 
    echo "$(date -d "$date $hour" +"%F - %R:%S") $rest"
  done | sort -h
2014-11-12 - 00:08:03 lat = 41.937911; lon = /93.369249<br>
2014-11-12 - 00:11:29 lat = 41.949656; lon = /93.329133<br>
2014-11-12 - 00:23:02 lat = 42.025385; lon = /93.347026<br>
2014-11-12 - 00:29:10 lat = 41.033341; lon = /93.380586<br>
2014-11-12 - 00:38:08 lat = 41.036720; lon = /93.436851<br>
2014-11-12 - 00:45:20 lat = 41.998129; lon = /93.400943<br>
2014-11-12 - 00:53:36 lat = 41.961489; lon = /93.414624<br>
2014-11-12 - 03:03:59 lat = 41.990516; lon = /93.430704<br>
2014-11-12 - 03:05:15 lat = 41.001546; lon = /93.443352<br>

This will work on your example but fails if you also need to sort February (02) before November (11). So, a trick would be to print the dates as seconds since the epoch, sort on that and then remove it:

$ sed 's#-#/#g' file | while read date _ hour rest; do 
  printf "%s\t%s %s\n" "$(date -d "$date $hour" +"%s")" "$date - $hour" "$rest"
done | sort | cut -f 2-
11/12/2014 - 12:08:03AM lat = 41.937911; lon = /93.369249<br>
11/12/2014 - 12:11:29AM lat = 41.949656; lon = /93.329133<br>
11/12/2014 - 12:23:02AM lat = 42.025385; lon = /93.347026<br>
11/12/2014 - 12:29:10AM lat = 41.033341; lon = /93.380586<br>
11/12/2014 - 12:38:08AM lat = 41.036720; lon = /93.436851<br>
11/12/2014 - 12:45:20AM lat = 41.998129; lon = /93.400943<br>
11/12/2014 - 12:53:36AM lat = 41.961489; lon = /93.414624<br>
11/12/2014 - 03:03:59AM lat = 41.990516; lon = /93.430704<br>
11/12/2014 - 03:05:15AM lat = 41.001546; lon = /93.443352<br>
11/12/2014 - 03:11:50AM lat = 42.039054; lon = /93.442001<br>

Or, to print the dates in 24H format:

$ sed 's#-#/#g' file | while read date _ hour rest; do 
    printf "%s\t%s %s\n" "$(date -d "$date $hour" +"%s")" \ 
    "$(date -d "$date $hour" +"%F - %R:%S")" "$rest"
  done | sort | cut -f 2-
2014-11-12 - 00:08:03 lat = 41.937911; lon = /93.369249<br>
2014-11-12 - 00:11:29 lat = 41.949656; lon = /93.329133<br>
2014-11-12 - 00:23:02 lat = 42.025385; lon = /93.347026<br>
2014-11-12 - 00:29:10 lat = 41.033341; lon = /93.380586<br>
2014-11-12 - 00:38:08 lat = 41.036720; lon = /93.436851<br>
2014-11-12 - 00:45:20 lat = 41.998129; lon = /93.400943<br>
2014-11-12 - 00:53:36 lat = 41.961489; lon = /93.414624<br>
2014-11-12 - 03:03:59 lat = 41.990516; lon = /93.430704<br>
2014-11-12 - 03:05:15 lat = 41.001546; lon = /93.443352<br>
2014-11-12 - 03:11:50 lat = 42.039054; lon = /93.442001<br>
| improve this answer | |
0

With python and the dateutil module (pip install dateutil), you can do the sorting directly on the datetime objects:

#! /usr/bin/env python
import sys
from dateutil.parser import parse

lines = []
for line in open(sys.argv[1]):
    d, rest = line[:24], line[24:]
    lines.append((parse(d), rest))

for x in sorted(lines):
    print x[0], x[1],

start with python program.py inputfile

This can be done without dateutil but the advantage of using it is that you don't have to specify the input time format, as long as it is not ambiguous.

| improve this answer | |
0

I would use awk with sort:

awk '{while("date +%T -d" $3|getline x){$3=x}}1' logfile | sort -t- -n -k3 -k1 -k2

Lets first modify your log a little bit to have different dates:

11-12-2010 - 03:03:59AM lat = 41.990516; lon = -93.430704
11-12-1998 - 03:05:15AM lat = 41.001546; lon = -93.443352
11-12-2030 - 03:11:50AM lat = 42.039054; lon = -93.442001
11-12-2014 - 12:08:03AM lat = 41.937911; lon = -93.369249
11-12-2014 - 12:11:29AM lat = 41.949656; lon = -93.329133
11-11-2014 - 12:23:02AM lat = 42.025385; lon = -93.347026
11-12-2011 - 12:29:10AM lat = 41.033341; lon = -93.380586
11-12-2011 - 12:38:08AM lat = 41.036720; lon = -93.436851
10-12-2014 - 12:45:20AM lat = 41.998129; lon = -93.400943
11-12-2014 - 12:53:36AM lat = 41.961489; lon = -93.414624

The result would be:

11-12-1998 - 03:05:15 lat = 41.001546; lon = -93.443352
11-12-2010 - 03:03:59 lat = 41.990516; lon = -93.430704
11-12-2011 - 00:29:10 lat = 41.033341; lon = -93.380586
11-12-2011 - 00:38:08 lat = 41.036720; lon = -93.436851
10-12-2014 - 00:45:20 lat = 41.998129; lon = -93.400943
11-11-2014 - 00:23:02 lat = 42.025385; lon = -93.347026
11-12-2014 - 00:08:03 lat = 41.937911; lon = -93.369249
11-12-2014 - 00:11:29 lat = 41.949656; lon = -93.329133
11-12-2014 - 00:53:36 lat = 41.961489; lon = -93.414624
11-12-2030 - 03:11:50 lat = 42.039054; lon = -93.442001

The trick here is to use - as a field separator and sort first on 3rd field (year) then on first (month), then on second (day). All in sorting are numerical (-n option).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.