org-mode: how to generate clock report for hours worked each day?

Question

org-clock-report is awesome; however, it reports time spent on each task. Is there any custom command that you use to generate a clock report showing hours worked, across tasks, for each day?

The trouble I've encountered is that, I file my TODO captures under a file+datetree entry. For example, one TODO task might be filed on Day -2, but I could be working on it -- i.e., clocking in and out -- on Day -2, -1, and today. So generating a table naively will just show something like:

#+BEGIN: clocktable :maxlevel 4 :scope file :block thisweek 
#+CAPTION: Clock summary at [2017-09-23 Sat 11:48], for week 2017-W38.
| Headline                              | Time      |         |       |       |
|---------------------------------------+-----------+---------+-------+-------|
| *Total time*                          | *1d 0:12* |         |       |       |
|---------------------------------------+-----------+---------+-------+-------|
| 2017                                  | 1d 0:12   |         |       |       |
| \_  2017-09 September                 |           | 1d 0:12 |       |       |
| \_    2017-09-11 Monday               |           |         |  1:23 |       |
| \_      look into AAA BBBB project    |           |         |       |  
| \_    2017-09-15 Friday               |           |         |  3:33 |       |
| \_      look into XXX YYYY project    |           |         |       |  3:33 |
#+END:

Note that the second TODO task was filed under 2017-09-15 Friday, which was the date I entered the task. But really, I might have worked on it on Friday, Saturday, etc. I just want another command to show how many hours I've worked on each day, generating a report such as the following:

2017-09-11 Monday     xx hours yy mins worked (across all tasks)
...
2017-09-15 Friday     zz hours tt mins worked (across all tasks)

Thanks in advance.

Answer 1

I think you might consider this to be a partial solution to your question.

If you include :step day in your clocktable line then you'll get several clock tables emitted -- one per day for the current week in your example.

While this answers the first part of your question of showing what tasks were worked on per day, you'll still see those tasks under a hierarchy based on your datetree structure of when you created those tasks. If you also include maxlevel 0 in the clocktable then you'll only see the total for each day without any tasks listed, which I think gets you the data you asked for although not quite in the simple list-format you wanted.

Link to the appropriate bit of the manual: http://orgmode.org/manual/The-clock-table.html

The second part of the question about whether TODO tasks are a good fit for a datetree structure is, I think, down to personal choice. On the one hand it makes it easy to locate tasks in a large file according to when it was added (although I suspect you could do the same thing with a custom agenda view), but on the other hand it prevents you grouping tasks into hierarchies for larger-scale projects. Choosing between those options depends on how you want to use org-mode.

My personal choice would be that datetrees are good for "journal"-type entries (e.g. notes on what was done each day), but with tasks filed in a project hierarchy, but there's no single right way.

Answer 2

The closest thing I can come out of this was writing your own custom dynamic block. You can read more about it at C-h i g (org)Dynamic blocks. This, however, requires you to know more about Org-mode internals. The following is a very minimalist callback to generate weekly report on day-by-day basis. The basic idea is there and you can extend it to suit your needs.

(require 'cl)
(require 'org-clock)
(defun org-dblock-write:weekly (params)
  (cl-flet ((fmttm (tm) (format-time-string (org-time-stamp-format t t) tm)))
    (let ((file (or (plist-get params :file) (buffer-file-name)))
          (start (seconds-to-time
                  (org-matcher-time (plist-get params :tstart))))
          (end (seconds-to-time (org-matcher-time (plist-get params :tend)))))
      (while (time-less-p start end)
        (let ((next-week (time-add start
                                   (date-to-time "1970-01-08T00:00Z")))
              (week-begin (line-beginning-position))
              (week-minutes 0))
          (insert "\nWeekly Table from " (fmttm start) "\n")
          (insert "| Day of Week | Time |\n|-\n")
          (while (time-less-p start next-week)
            (let* ((next-day (time-add start (date-to-time "1970-01-02T00:00Z")))
                   (minutes
                    (with-current-buffer (find-file-noselect file)
                      (cadr (org-clock-get-table-data
                             file
                             (list :maxlevel 0
                                   :tstart (fmttm start)
                                   :tend (fmttm next-day)))))))
              (insert "|" (format-time-string "%a" start)
                      "|" (format "%d" minutes)
                      "|\n")
              (org-table-align)
              (incf week-minutes minutes)
              (setq start next-day)))
          (when (equal week-minutes 0)
            (delete-region week-begin (line-beginning-position))))))))

Keep in mind that you have to specificy :tstart and :tend for the above function. The day of week for :tstart is also taken as the start of the week and weeks without any clocked time is pruned from the results.

Put the following empty block on the file where you want to generate weekly report and press C-c C-c with cursor on the #+BEGIN line.

#+BEGIN: weekly :tstart "<2020-05-11>" :tend "<now>"
#+END:

On the file I recently worked on, it generates the following results.

#+BEGIN: weekly :tstart "<2020-05-11>" :tend "<now>"

Weekly Table from [2020-05-11 Mon 00:00]
| Day of Week | Time |
|-------------+------|
| Mon         |    0 |
| Tue         |    0 |
| Wed         |    0 |
| Thu         |    0 |
| Fri         |  104 |
| Sat         |   94 |
| Sun         |   40 |

Weekly Table from [2020-05-18 Mon 00:00]
| Day of Week | Time |
|-------------+------|
| Mon         |    0 |
| Tue         |  168 |
| Wed         |  310 |
| Thu         |  147 |
| Fri         |  158 |
| Sat         |  332 |
| Sun         |  286 |

Weekly Table from [2020-05-25 Mon 00:00]
| Day of Week | Time |
|-------------+------|
| Mon         |  446 |
| Tue         |  310 |
| Wed         |    4 |
| Thu         |  141 |
| Fri         |  388 |
| Sat         |  335 |
| Sun         |  335 |

Weekly Table from [2020-06-01 Mon 00:00]
| Day of Week | Time |
|-------------+------|
| Mon         |  480 |
| Tue         |    0 |
| Wed         |    0 |
| Thu         |   73 |
| Fri         |  296 |
| Sat         |  202 |
| Sun         |  270 |

Weekly Table from [2020-06-08 Mon 00:00]
| Day of Week | Time |
|-------------+------|
| Mon         |  382 |
| Tue         |    0 |
| Wed         |  106 |
| Thu         |  159 |
| Fri         |  226 |
| Sat         |  319 |
| Sun         |  255 |

Weekly Table from [2020-06-15 Mon 00:00]
| Day of Week | Time |
|-------------+------|
| Mon         |   44 |
| Tue         |  146 |
| Wed         |   92 |
| Thu         |  182 |
| Fri         |  147 |
| Sat         |   36 |
| Sun         |  216 |

#+END:

Update:

If you have a working R environment, you can have just more fun.

(defun org-dblock-write:weekly (params)
  (cl-flet ((fmttm (tm) (format-time-string (org-time-stamp-format t t) tm))
            (fmtwk (tm) (format-time-string "Y%gW%V" tm)))
    (let ((file (or (plist-get params :file) (buffer-file-name)))
          (start (seconds-to-time
                  (org-matcher-time (plist-get params :tstart))))
          (end (seconds-to-time (org-matcher-time (plist-get params :tend)))))
      (while (time-less-p start end)
        (let ((next-week (time-add start
                                   (date-to-time "1970-01-08T00:00Z")))
              (week-begin (line-beginning-position))
              (week-minutes 0)
              (week-start (fmttm start))
              (week-name (fmtwk start)))
          (insert "\n#+NAME: " week-name "\n"
                  "#+CAPTION: Weekly Table from " week-start "\n"
                  "| Day of Week | Time |\n|-\n")
          (while (time-less-p start next-week)
            (let* ((next-day (time-add start (date-to-time "1970-01-02T00:00Z")))
                   (minutes
                    (with-current-buffer (find-file-noselect file)
                      (cadr (org-clock-get-table-data
                             file
                             (list :maxlevel 0
                                   :tstart (fmttm start)
                                   :tend (fmttm next-day)))))))
              (insert "|" (format-time-string "%a" start)
                      "|" (format "%d" minutes)
                      "|\n")
              (incf week-minutes minutes)
              (setq start next-day)))
          (org-table-align)
          (insert "\n#+begin_src R :results graphics :file " week-name ".pdf :var w=" week-name "\n"
                  "  barplot(w[,2]/60, names.arg = w[,1], ylim=c(0,9))\n"
                  "#+end_src\n")
          (org-babel-execute-src-block-maybe)
          (forward-line 2)
          ;; (delete-region week-begin (line-beginning-position)) ; If you want only the charts
          (insert "\n#+CAPTION: Weekly Chart from " week-start "\n"
                  "#+ATTR_LATEX: :width 320pt\n"
                  "[[file:" week-name ".pdf]]\n")
          (when (equal week-minutes 0)
            (delete-region week-begin (line-beginning-position))))))))

Run C-c C-c on the custom dynamic block and then export with C-c C-e l o. You get a PDF file with weekly chart report.

The following is a snapshot from my export.