Query tasks that i spent time on (LOGBOOK) for each day (and display it in journal)

Questions & Help Queries
1

First my motivation

  • Most of my tasks spread over weeks, some over month and few over years
  • I use Logbook for timetracking
  • I want to see what i did each day in journal and how much time i spent on the task but not type the tasks/TODOs over and over again

The idea

  • Have a page with my tasks/todos or just spread them around anywhere
  • (maybe) add a template for journal that query logseq/logbook and displays tasks where time was spent on for that day.

questions

  • is it possible? i havent been able to find an example that queries the time spent on a task on a specific day
  • nor did i find how to get the date of a journal entry
2

Welcome.

For reference:

If you mean the name of the journal that the entry is in, this is certainly possible.

3

I would believe you’re looking for a variation of this one:
https://siferiax.github.io/#/page/logseq%2Fquery%20tests/block/âś…%20repeating%20tasks%20done%20today

In this case I use a quirk of repeating tasks, but that doesn’t matter for your use case as you’re always logging time and thus all tasks have a logbook entry.

So you need a regex close to this one:
https://siferiax.github.io/#/page/651bd1fc-49c8-42a3-997c-1382da3c8e9b

Something like this perhaps?

#+BEGIN_QUERY
{:title [:b "Worked on tasks of this journal day"]
 :inputs [:query-page]
 :query [:find (pull ?b [*])
  :in $ ?page
  :where
   [?p :block/name ?page]
   [?p :block/journal-day ?day]
   [(str ?day) ?strday]
   [?b :block/marker _]
   [?b :block/content ?c]
   [(re-pattern "LOGBOOK:\\nCLOCK:\\s\\[(\\d{4})-(\\d{2})-(\\d{2})\\s\\w+\\s(\\d{2}):(\\d{2}):(\\d{2})") ?rx]
   [(re-find ?rx ?c) [_ ?yyyy ?mm ?dd ?h ?m ?s]]
   [(str ?yyyy ?mm ?dd) ?date]
   [(= ?date ?strday)]
 ]
}
#+END_QUERY

image
image832Ă—331 17.9 KB

It will take the journal date from the journal page the query is on and show which tasks have time tracked on this day.

1 Like
4

Thank you very much @Siferiax for the almost perfect solution! I would not have been able to come up with this solution (at least this year :wink: ).

It takes quite some effort to get to know the datascript/coljure commands and the database schema logseq uses.

With your brilliant starting point i made three improvements:

  • Remove LOGBOOK from the regex pattern so it does not only match the very first entry
  • Remove time, because not necessary for matching
  • use re-seq instead of re-find to process all matches
#+BEGIN_QUERY
{:title [:b "Worked on tasks of this journal day"]
 :inputs [:query-page]
 :query [:find (pull ?b [*])
  :in $ ?page
  :where
   [?p :block/name ?page]
   [?p :block/journal-day ?day]
   [(str ?day) ?strday]
   [?b :block/marker _]
   [?b :block/content ?c]
   [(re-pattern "CLOCK:\\s\\[(\\d{4})-(\\d{2})-(\\d{2})") ?rx]
   [(re-seq ?rx ?c)  ([_ ?yyyy ?mm ?dd])]
   [(str ?yyyy ?mm ?dd) ?date]
   [(= ?date ?strday)]
 ]
}
#+END_QUERY

Now the query tasks shows the tasks that i spent time on for that day.
What it does not do (yet) show the time that i spent on that day. It now shows the complete block.
If i would like to calculate the time i spent on that day. i would probably:

  • extend the regex to match the time spent for a CLOCK: entry in the block: => hh:mm:ss
  • find out how to do math operations in coljure/datascript and sum up matched times
  • somehow put the result in :find (pull ?b [*])
1 Like
5

Good to hear you could improve upon my initial concept! :+1:t3:

So math you can do in simple statements as [(+ ?var1 ?var2) ?result]
I just don’t know how that works in the context of re-seq and multimatches. :woman_shrugging:t4:
You have to fiddle around with that.

You can find any variable actually.
:find ?var
It’ll spit out the value only this way. Valuable for debugging as well.
You can also add :keys keyname underneath :find to define names for the variables.
Multiple are possible as well.

:find ?var1 ?var1
:keys name1 name2
6

i am trying to achieve similar, can you point out how to get a list of all CLOCK entries per task per month per tag?

7

I want to know this too, but am verrrrrry out of my depth here!

Essentially I just want to be able to add to my daily journal template a (query) section that checks all logbook entries across all my tasks and displays the name of the task and how much time it was in “DOING” state that day (i.e., how much time I worked on it that day).

It would be kind of cool if I could also exclude tasks from certain pages, but it’s by no means a priority.

It sounds like all this is possible, but I don’t even know where to start :sweat_smile: