0

I have a tab separated file and the date columns contains date with two different formats. I am using this sort command to sort by one format type ("%a %b %d %H:%M:%S %Z %Y"), but is there any way to sort by the date column regardless of it is format? Thanks!

Input

date
Mon Mar 02 03:56:26 UTC 2020
2020-03-03 15:46:52

sort -t$'\t' -k 3.25,3.28n -k 3.5,3.7M -k 3.9,3.10n -k 3.12,3.13n -k 3.15,3.16n -k3.18,3.19n sample.csv
2
  • Do you want to keep both formats? You could convert your first format to YYYY-MM-DD HH:MM:SS and sort lexicographically (sort -k3,3).
    – Freddy
    Commented May 7, 2020 at 0:28
  • @Freddy Not necessary. How can i do that?
    – Alex Man
    Commented May 7, 2020 at 0:31

2 Answers 2

1

You could convert both formats to something unambiguous like epoch time, sort on that, then discard. For example, using Miller

mlr --tsv put '
  $epoch = ($date =~ "^[A-Z][a-z][a-z]") ? strptime($date,"%a %b %d %H:%M:%S %Z %Y") : strptime($date,"%Y-%m-%d %H:%M:%S")
' then sort -n epoch then cut -f date input
0

Try this to convert your "%a %b %d %H:%M:%S %Z %Y" format (or any other format that is recognized by date -d) to %F %T (%Y-%m-%d %H:%M:%S) using awk and the date command.

The field number to convert is given in awk variable col and the field is skipped if it is already in the correct format. Well, you can remove the check, but obviously that would make the script slower.

awk -v col=3 '
  BEGIN{ FS=OFS="\t" }

  # or remove the check and begin the next line with an opening `{`
  $(col) !~ /^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$/{
    cmd="date -u -d \"" $(col) "\" +\"%F %T\"" 
    cmd | getline $(col)
    close(cmd)
    $0=$0
  }
  1
' infile > outfile

Then use a lexicographic sort on the desired field, e.g. sort -t$'\t' -k3,3 file.

1
  • I added the -u option to convert the time to UTC, remove it if you need your local time.
    – Freddy
    Commented May 7, 2020 at 2:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .