4

windows 10, emacs 26.1 I have table in org file and I want to sort table by column Date

Here table:

|        Number |       Date |
|---------------+------------|
| 1535749200000 | 31.08.2018 |
| 1535835600000 | 01.09.2018 |
| 1590094800000 | 21.05.2020 |
| 1536019200000 | 04.09.2018 |
| 1536883200000 | 14.09.2018 |

Note: Date is in format "dd.MM.yyyy"

Here my steps:

M-x org-sort

enter image description here enter image description here

Then press t

Here result. enter image description here

As you can see it's not sort. And I get message:

You can run the command ‘org-sort’ with C-c ^
2
  • you make it difficult to reproduce by attaching images and not text that could be pasted for testing. first thing I see is that your dates are not datestamps.. Dec 3, 2018 at 10:48
  • @manandearth I update my post. Add table as text. Dec 3, 2018 at 11:22

2 Answers 2

4

If you don't want to have a third column, you can also define a function for sorting DD.MM.YYYY formated dates like so:

(defun compare-german-date (str)
  "Convert DD.MM.YYYY to sortable YYYYMMDD."
 ( setq stringslist  (delete "" (split-string str "\\.")))
 (string-to-number (concat (nth 2 stringslist) (nth 1 stringslist) (nth 0 stringslist)) )
)

then you can use org-sort with "f" for function and provide compare-german-date there (2nd function can stay default).

This doesn't change your table entries, it provides a key for sort. So your table will not have YYYYMMDD, but 'll stay DD.MM.YYYY.

Beware using it for other columns than just | DD.MM.YYYY |

You might consider using manandearth's answer though, because having timestamps could be very handy integrating data with org. Or maybe – if you don't like timestamp – consider using ISO-formated dates (as you could easyly sort them alphabetical).

1
  • sorry for lot's of edits - made some c&p mistakes ^^ should work now…
    – kai-dj
    Dec 3, 2018 at 12:20
4

org-sort by date will work with timestamps. For example:

| number | date             |                                                                                                                 
|--------+------------------|                                                                                                                 
|    781 | <2018-12-07 Fri> |                                                                                                                                                                                                                                 
|    129 | <2018-12-03 Mon> |                                                                                                                 
|    321 | <2018-11-23 Fri> | 
|    123 | <2018-12-04 Tue> | 

With the caret in the date column type: C-c^ followed by t for sorting by time.

Wil result in:

| number | date             |                                                                                                                 
|--------+------------------|                                                                                                                 
|    781 | <2018-12-07 Fri> |                                                                                                                 
|    123 | <2018-12-04 Tue> |                                                                                                                 
|    129 | <2018-12-03 Mon> |                                                                                                                 
|    321 | <2018-11-23 Fri> |

EDIT

In your case you will need to reformat those strings using regular expressions.

You can write a function such as:

(defun format-date-column (d)                                                                                                                     
   (concat "<" (replace-regexp-in-string   "[.]" "-" d) ">")) 

and then apply it in the table formula field. In the following example I made a third column and the function parses between columns:

#+tblfm: $3 = '(format-date-column $2)

Turning this table:

|        Number |       Date | timestamps |                                                                                                 
|---------------+------------+------------|                                                                                                 
| 1535749200000 | 31.08.2018 |            |                                                                                                 
| 1535835600000 | 01.09.2018 |            |                                                                                                 
| 1590094800000 | 21.05.2020 |            |                                                                                                 
| 1536019200000 | 04.09.2018 |            |                                                                                                 
| 1536883200000 | 14.09.2018 |            |

To:

|        Number |       Date | datestamps   |                                                                                                 
|---------------+------------+--------------|                                                                                                 
| 1535749200000 | 31.08.2018 | <31-08-2018> |                                                                                                 
| 1535835600000 | 01.09.2018 | <01-09-2018> |                                                                                                    
| 1590094800000 | 21.05.2020 | <21-05-2020> |                                                                                                 
| 1536019200000 | 04.09.2018 | <04-09-2018> |                                                                                                 
| 1536883200000 | 14.09.2018 | <14-09-2018> |    

Which can then be sorted as explained above.

2
  • Not work in my case, because date format is dd.MM.yyyy. Org mode sort only if date is in format yyyy-MM-dd. How I can fix this? Dec 3, 2018 at 11:24
  • Now included in the edit. Dec 3, 2018 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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