2

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?

1 Answer 1

5

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.

5
  • suppose I has 1000 lines in table. How I can convert all of them to desire date format? Dec 6, 2018 at 17:57
  • And is it possible to to convert dates not only in org mode? E.g. in CSV file? Dec 6, 2018 at 18:03
  • 1
    @Alexei see edits, otherwise be more explicit with your questions.
    – jue
    Dec 6, 2018 at 19:31
  • I press C-c C-c but nothing happened. Only message in minibuffer "Local setup has been refreshed" Dec 7, 2018 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, 2018 at 16:49

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.