Sort lines according to date and time

Question

I have this file which represents a calender. Each file has the exact date and time, then the name event and a note.

06-12-2016,12:00,gym,leg day
05-04-2018,12:09,gym,hands
09-08-2019,13:08,movies,horror
05-04-2019,14:07,gym,hands
23-03-2018,16:47,dance class
25-04-2019,13:29,dance class
05-12-2017,14:45,songwriting

I need to print the events sorted with the correct order.

I have tried with grep and awk but it didn't work correctly. What can I do?

Solution 1

I believe the simplest command is

sort -t- -k3.1,3.4 -k2,2 file

This sorts on four characters of the third field and resolve ties by sorting on the second field. If ties are still not solved the order will be determined automatically by comparing all other fields starting from the first one.

Notice that all sorting is done alphabetically because numbers are positive integers so adding additional n doesn't matter.

Test sample:

06-12-2016,12:00,gym,leg day
05-04-2018,12:09,gym,hands
09-08-2019,13:11,movies,horror
09-08-2019,13:09,movies,horror
09-08-2019,13:08,movies,horror
08-08-2019,13:09,movies,horror
05-04-2019,14:07,gym,hands
23-03-2018,16:47,dance class
25-04-2019,13:29,dance class
05-12-2017,14:45,songwriting

Sorted result:

06-12-2016,12:00,gym,leg day
05-12-2017,14:45,songwriting
23-03-2018,16:47,dance class
05-04-2018,12:09,gym,hands
05-04-2019,14:07,gym,hands
25-04-2019,13:29,dance class
08-08-2019,13:09,movies,horror
09-08-2019,13:08,movies,horror
09-08-2019,13:09,movies,horror
09-08-2019,13:11,movies,horror

Solution 2

One way would be to manually pick out all of the sort fields:

sort -t, -k 1.7,1.10n -k 1.4,1.5n -k 1.1,1.2n -k 2.1,2.2n -k 2.4,2.5n input

This splits the lines up based on comma-separated fields, then uses the positions of the year, month, day, hour, and minutes to sort numerically. Sample output:

06-12-2016,12:00,gym,leg day
05-12-2017,14:45,songwriting
23-03-2018,16:47,dance class
05-04-2018,12:09,gym,hands
05-04-2019,14:07,gym,hands
25-04-2019,13:29,dance class
09-08-2019,13:08,movies,horror

Comments

[Marek Zakrzewski]

Can you please show what you tried with grep? What the desired output will be and so on?

[Kusalananda]

What is the correct order, by timestamp or by name? If by timestamp, this would have been so much easier if you had only used YYYY-MM-DD dates...

[Dougie]

Try a unix sort sort -n -t\- -k 3.1 -k 2.1 -k 1.1 file

[Jeff Schaller]

@Dougie, please try to restrain yourself from "Answering questions in comments" (as the stock text for comments says). If you have a solution, please consider posting an Answer, instead. Thank you!

[Jeff Schaller]

What's an example of an activity that happens before 10 am? Is it 06-12-2016,09:00,teatime or is it 06-12-2016,9:00,teatime?

[Rich]

Are you the file format owner? Can you correctly arrange the time fields so that they go most-to-least significant? I.e. YYYY-MM-DD,HH:MM, or following ISO8601, YYYY-MM-DD_T_HH:MM -- that way you could use a simple sort command; or if they were filenames, ls.

[Arthur Accioly]

It's the first time that I'm seeing -k being used with dots. Can you please let me know what's the role of the dots on this part of the command: "3.1,3.4"? Thank you.

[jimmij]

@ArthurAccioly number before the dot is a field, number after the dot is a character, so 3.1,3.4 means to sort on the third field from the first to fourth character. i.e. in this example the year.