Windows 10, Emacs 26.1

In org file:

| _id |          Date |   expiresDate |   amount |
|-----+---------------+---------------+----------|
| 633 | 1539190765000 | 1573336800000 |   766.26 |
| 184 | 1542045137000 | 1576101600000 |    851.4 |
| 110 | 1542348620000 | 1576447200000 |   936.54 |
| 634 | 1539190765000 | 1573336800000 | 40788.33 |
| 185 | 1542045137000 | 1576101600000 | 41041.81 |
|   7 | 1543213783000 | 1577311200000 | 41295.29 |
| 756 | 1538629706000 | 1573768800000 |    177.0 |

In columns "Date" and "expiresDate" dates are in milliseconds (from January 1, 1970, 00:00:00 GMT). E.g. 1539190765000 = 10.10.2018 16:59

I use this site for convert https://www.fileformat.info/tip/java/date2millis.htm

So I need to convert both dates columns to human readable format: YYYY-MM-dd HH:mm

So the result must be like this:

| _id | Date               | expiresDate        | amount |
|-----+--------------------+--------------------+--------|
| 633 | <2018-10-10 16:59> | <2019-11-19 22:00> | 766.26 |
...

How I can do this?

up vote 4 down vote accepted

You could use calc function unixtime in a table formula.
So, first add 2 additional columns to your table:

| _id |          Date | Date hrf               |   expiresDate | epires Date hrf        |   amount |
|-----+---------------+------------------------+---------------+------------------------+----------|
| 633 | 1539190765000 |                        | 1573336800000 |                        |   766.26 |
...

Then add following formula at the end of your table and evaluate it:

#+TBLFM: $3=unixtime($2/1000)::$5=unixtime($4/1000)

Now every line of those extra columns get filled with the converted dates. You could delete non needed columns afterwards.

unixtime calculates from seconds (since 1.Jan.1970) to human readable format. So, to be able to use unixtime you need to convert milliseconds to seconds.


Edit, as reply to comments: Use following two table formulas (with my table structure from above) to get exactly the date string you wanted:

#+TBLFM: $3='(format-time-string "<%F %H:%M>" (seconds-to-time (/ $2 1000)) t);N
#+TBLFM: $5='(format-time-string "<%F %H:%M>" (seconds-to-time (/ $4 1000)) t);N

Put point (the cursor) at the formula line (which should be right below the table) and press C-c C-c.

Some explanation of the two table formulas above:

  • the formula consists of elisp code (not calc functions)
  • the ;N tells org-mode to feed the elisp code with a number (instead of a string)
  • milliseconds need to be converted to seconds, therefore the division by 1000

To just convert a single ms value, use following elisp function:

(defun my-ms2date-convert (ms)
  (interactive)
  (format-time-string "<%F %H:%M>" (seconds-to-time (/ ms 1000)) t))

Of course you could use this function for csv files, but you have to figure out yourself how to process every single value from a csv table.

  • suppose I has 1000 lines in table. How I can convert all of them to desire date format? – Alexei Dec 6 at 17:57
  • And is it possible to to convert dates not only in org mode? E.g. in CSV file? – Alexei Dec 6 at 18:03
  • 1
    @Alexei see edits, otherwise be more explicit with your questions. – jue Dec 6 at 19:31
  • I press C-c C-c but nothing happened. Only message in minibuffer "Local setup has been refreshed" – Alexei Dec 7 at 16:41
  • @Alexei put the formula right below the table, and the cursor at the formula before pressing 'C-c C-c'. Short introduction to tables and formulas is here: orgmode.org/worg/org-tutorials/org-spreadsheet-intro.html – jue Dec 10 at 16:49

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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