Org Mode: Exporting Clock Tables

The Emacs Cat

Tuesday, Aug 1, 2023

2023

After reading a quite interesting post by Irreal on Org mode clock tables, I decided to share my — a bit specific — experience with the subject.

The Task

Like many of us these days, I’m working remotely. My employer requires a periodic report and I should track my time I spend on various task — exactly as Irreal describes in his post. Of course, I’m using Org mode clock tables for that purpose.

#+TITLE: Worktime Tracker for July 2023
#+OPTIONS: num:nil toc:nil

#+BEGIN: clocktable :maxlevel 3 :scope file
#+END

* 2023-07    :2023:

** 2023-07-03
*** Impl. a test gRPC server                            :TEST:
:LOGBOOK:
CLOCK: [2023-07-03 Mon 15:05]--[2023-07-03 Mon 15:50] =>  0:45
CLOCK: [2023-07-03 Mon 09:45]--[2023-07-03 Mon 14:10] =>  4:25
:END:

** 2023-07-04
*** Fix client timeout                                   :FIX:
:LOGBOOK:
CLOCK: [2023-07-04 Tue 12:45]--[2023-07-04 Tue 13:10] =>  0:25
:END:
*** Add AddOrder handler                                :FEAT:
:LOGBOOK:
CLOCK: [2023-07-04 Tue 14:05]--[2023-07-04 Tue 15:10] =>  1:05
:END:

Then if I put the cursor at the line with the word #+BEGIN (line #4 in my org file) and press C-c C-c (in the Org mode, this chord runs the multipurpose command org-ctrl-c-ctrl-c), the dynamic clock table block is getting updated immediately and I get a standard clock table report as shown below.

#+BEGIN: clocktable :maxlevel 3 :scope file
#+CAPTION: Clock summary at [2023-07-30 Sun 15:32]
| Headline                       | Time |      |      |
|--------------------------------+------+------+------|
| *Total time*                   |*6:40*|      |      |
|--------------------------------+------+------+------|
| 2023-07                        | 6:40 |      |      |
| \_  2023-07-03                 |      | 5:10 |      |
| \_    Impl. a test gRPC server |      |      | 5:10 |
| \_  2023-07-04                 |      | 1:30 |      |
| \_    Fix client timeout       |      |      | 0:25 |
| \_    Add AddOrder handler     |      |      | 1:05 |
#+END

So far, everything looks fine.

The Problem

The problem is that my employer wants the report to be in a specific format.

The required MS Word (sic!) file with the work time report.

Please note the column #1 should contain a tag of the task, all dates should be in the DD.MM.YYYY format, a time spent for the task should be in the hh:mm format, left-padded with 0s if required, and the total time should be in the hhh:mm format, left-padded with 0s as well.

Here the adventure begins.

The Adventure

I decided to implement my exporter as the Elisp code block inside my time tracking org file. Therefore, if I press C-c C-c when the cursor is over the exporter code block, Emacs evaluates this block and prints the result just below the block. This would allow us to make some experiments.

First of all, I need an access to my clock table entries. After some research I found what I need — org-clock-get-table-data, a built-in function defined in the org-clock.el[.gz] file (as of Emacs 28.2).

(defun org-clock-get-table-data (file params)
  "Get the clocktable data for file FILE, with parameters PARAMS.
FILE is only for identification - this function assumes that
the correct buffer is current, and that the wanted restriction is
in place.
The return value will be a list with the file name and the total
file time (in minutes) as 1st and 2nd elements.  The third element
of this list will be a list of headline entries.  Each entry has the
following structure:

  (LEVEL HEADLINE TAGS TIMESTAMP TIME PROPERTIES)

LEVEL:      The level of the headline, as an integer.  This will be
            the reduced level, so 1,2,3,... even if only odd levels
            are being used.
HEADLINE:   The text of the headline.  Depending on PARAMS, this may
            already be formatted like a link.
TAGS:       The list of tags of the headline.
TIMESTAMP:  If PARAMS require it, this will be a time stamp found in the
            entry, any of SCHEDULED, DEADLINE, NORMAL, or first inactive,
            in this sequence.
TIME:       The sum of all time spend in this tree, in minutes.  This time
            will of cause be restricted to the time block and tags match
            specified in PARAMS.
PROPERTIES: The list properties specified in the `:properties' parameter
            along with their value, as an alist following the pattern
            (NAME . VALUE)."

Let’s make an experiment.

#+begin_src elisp
(org-clock-get-table-data buffer-file-name '(:tags t))
#+end_src

#+RESULTS:
| /home/magnolia/Desktop/demo.org | 400 | ((1 2023-07 (2023) nil 400 nil) (2 2023-07-03 (2023) nil 310 nil) (3 Impl. a test gRPC server (2023 TEST) nil 310 nil) (2 2023-07-04 (2023) nil 90 nil) (3 Fix client timeout (2023 FIX) nil 25 nil) (3 Add AddOrder handler (2023 FEAT) nil 65 nil)) |

Please note the output is an Org table because the result is a list. Please also note the '(:tags t) parameter — it is required because we want all tags to be included in the output.

Obviously, we actually want the 3rd element of the output list.

#+begin_src elisp
(nth 2 (org-clock-get-table-data buffer-file-name '(:tags t)))
#+end_src

#+RESULTS:
| 1 |                  2023-07 | (2023)      | nil | 400 | nil |
| 2 |               2023-07-03 | (2023)      | nil | 310 | nil |
| 3 | Impl. a test gRPC server | (2023 TEST) | nil | 310 | nil |
| 2 |               2023-07-04 | (2023)      | nil |  90 | nil |
| 3 |       Fix client timeout | (2023 FIX)  | nil |  25 | nil |
| 3 |     Add AddOrder handler | (2023 FEAT) | nil |  65 | nil |

It seems we are on the right way. The column #1 contains the level of the headline, as an integer; #2 — the text of the headline; #3 — the list of tags of the headline; #5 — the sum of all time spend in this tree, in minutes, as an integer.

The Magic

Before implementing the clock table parser, we need some utilities.

First of all we should convert a date from the ISO 8601 format (YYYY-MM-DD) to the European DD.MM.YYYY format. According to the GNU Emacs Lisp Reference Manual, we can use two built-in functions for this purpose:

Function: format-time-string format-string &optional time zone

This function converts time (which should be a Lisp timestamp, and defaults to the current time if time is omitted or nil) to a string according to format-string.

and

Function: date-to-time string

This function parses the time-string string and returns the corresponding Lisp timestamp. This function assumes Universal Time if string lacks explicit time zone information, and assumes earliest values if string lacks month, day, or time.

As we’ll see below, the last sentence (in italic) is wrong — at least as of Emacs 28.2. Maybe it has something to do with this bug in Emacs 28, I don’t know.

Okay, let’s test it out.

#+begin_src elisp
  (format-time-string "%d.%m.%Y" (date-to-time "2023-07"))
#+end_src

Error: date-to-time: Invalid date: 2023-07

This doesn’t work.

#+begin_src elisp
  (format-time-string "%d.%m.%Y" (date-to-time "2023-07-04"))
#+end_src

Error: date-to-time: Invalid date: 2023-07-04

This doesn’t work as well.

This is where the magic begins. Let’s add a fake time component T01 to the ISO date string.

#+begin_src elisp
  (format-time-string "%d.%m.%Y" (date-to-time "2023-07-04T01"))
#+end_src

#+RESULTS:
: 04.07.2023

It worked, I’m so happy.

Now let’s implement a utility function that converts a time, in minutes, as an integer, to the properly padded string.

#+begin_src elisp
  (defun my/clock--format-time (time-in-minutes &optional padding)
  "Convert TIME-IN-MINUTES, as an integer, to a string,
   with hours left-padded with zeroes. Default PADDING is 2."
    (let* ((hours (/ time-in-minutes 60))
           (minutes (- time-in-minutes (* hours 60))))
      (format (concat "%0" (format "%d" (or padding 2)) "d:%02d")
               hours minutes)))

;; TEST
(my/clock--format-time 310)
#+end_src

#+RESULTS:
: 05:10

Okay, it works.

The Result

Now let’s combine everything together and then evaluate the code block.

#+begin_src elisp
  (require 'org-clock)

  (defvar my/cur-date nil "Holds the current entry date.")

  (defun my/clock--format-time (time-in-minutes &optional padding)
  "Convert TIME-IN-MINUTES, as an integer, to a string,
   with hours left-padded with zeroes. Default PADDING is 2."
    (let* ((hours (/ time-in-minutes 60))
           (minutes (- time-in-minutes (* hours 60))))
      (format (concat "%0" (format "%d" (or padding 2)) "d:%02d")
               hours minutes)))

  (defun my/clock--parse-entry (entry)
  "Parse the clock table ENTRY, returning a list of row items,
   depending on the level of the headline."
    (let* ((level (car entry))
           (headline (nth 1 entry))
           (tag (nth 1 (nth 2 entry)))
           (minutes (nth 4 entry))
           (date (if (= level 2)
                     (format-time-string "%d.%m.%Y"
                         (date-to-time (concat headline "T01"))))))
      (cond
        ((= level 1) (list headline "TOTAL" "" (my/clock--format-time minutes 3)))
        ((= level 2) (progn (setq my/cur-date date) nil))
        ((= level 3) (list tag headline my/cur-date (my/clock--format-time minutes)))
      )))

  ;; Apply the parser to every entry in the clock table.
  (mapcar #'my/clock--parse-entry
     (nth 2 (org-clock-get-table-data buffer-file-name '(:tags t))))
#+end_src

#+RESULTS:
| 2023-07 | TOTAL                    |            | 006:40 |
| TEST    | Impl. a test gRPC server | 03.07.2023 |  05:10 |
| FIX     | Fix client timeout       | 04.07.2023 |  00:25 |
| FEAT    | Add AddOrder handler     | 04.07.2023 |  01:05 |

Bingo! We’ve got what we exactly want — the Org mode table filled with properly formatted data. Now I can just copy this table from my work time org file to another org file and export it to the ODT file (and then save it as the MS Word file).

The MS Word file with the work time report.

Tested on Ubuntu Linux (Pop!_OS 20.10) and MS Windows 10 using Emacs 28.2.

Conclusions

We can have access to all the data collected in a clock table.
Org mode code blocks provide an ideal way to make experiments, testing your code, and getting the result inplace.
In Emacs, you can do everything.

Happy emacsing!