Sort a column with two different datetime format

Question

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

Answer 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

Answer 2

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.