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