Logseq Advanced Query - Timecard

I’ve been using Logseq for the past several months and I mostly love it. I’ve been tracking some work in it, and also tracking my time in it. I hate converting minutes to fractional hours, so I decided to script it in Logseq.

Logseq has a few built-in features:

• /calculator - This pulls up a simple calculator that prints the result on each line. Helpful, but I still need to do calculations like (15 + 23/60) - (13 + 13/60) to find the time between 15:23 and 13:13.
• ClojureScript Eval in a Block - You can create a clojure codeblock with the :results tag and it will evaluate it. Very slick, if I knew anything about ClojureScript at least. It’s so weird that I kept looking.
• Run Python & Javascript in Logseq - This looks really interesting, but it was buggy for me and not supported at all. I dream of a jupyter notebook style plugin for Logseq, this is a step in that direction.
• Advanced Queries - very powerful, natively supported, and with terrible documetnation. This was it.

My final advanced query does the following:

• Finds any of its children blocks that aren’t empty and pass a regex test for the format HH[:]MM HH[:]MM
• Calculates each time period
• Prints them out, with the sum at the bottom
• Optionally, each entry can have a note, which will be printed on the list as well. Useful for annotating what was done during the time period.

So simple! But because the documentation was so poor, and datalog is so alien to me, I struggled.

The next step is to figure out a way to dynamically update the query title with the total time so that it is useful when collapsed, but I don’t think that’s possible yet.

Final product:

#+BEGIN_QUERY
{:title "Timecard"
 :query [:find ?content
         :in $ ?current-block %
         :where
         [?child :block/parent ?current-block]
         [?child :block/content ?content]
         (time-content ?content)]
 :inputs [:current-block]
 :rules [
   [(time-content ?content)
    [(not= ?content "")]
    [(str "\\s*(\\d{1,2}):?(\\d{2})\\s+(\\d{1,2}):?(\\d{2})\\s*") ?pattern]
    [(re-pattern ?pattern) ?regex]
    [(re-find ?regex ?content)]]]
 :view (fn [results]
         (let [pattern (re-pattern "\\s*(\\d{1,2}):?(\\d{2})\\s+(\\d{1,2}):?(\\d{2})\\s*(.*)")
              h-to-m (fn [h]
                           (* h 60))
              format-duration (fn [mins]
                           (str (quot mins 60) "h " (mod mins 60) "m"))
              format-time (fn [h m]
                            (str (if (< h 10) (str "0" h) h) ":" 
                                 (if (< m 10) (str "0" m) m)))
               processed (for [content results]
                           (let [match (re-find pattern content)]
                             (when match
                               (let [[_ start-hour start-minute end-hour end-minute note] match
                                    start-h (read-string start-hour)
                                    start-m (read-string start-minute)
                                    end-h (read-string end-hour)
                                    end-m (read-string end-minute)
                                    start-total-mins (+ (h-to-m start-h) start-m)
                                    end-total-mins (+ (h-to-m end-h) end-m)
                                    duration-mins (- end-total-mins start-total-mins)
                                    formatted-span (str (format-time start-h start-m) " → " (format-time end-h end-m))]
                                 {:span formatted-span :duration duration-mins :note note}))))
               valid-entries (filter identity processed)
               total-mins (apply + (map :duration valid-entries))]
           [:div 
            [:table
             [:tbody
              (for [entry valid-entries]
                [:tr
                 [:td (:span entry)]
                 [:td (format-duration (:duration entry))]
                 [:td [:em {:style {:color "gray"}} (:note entry)]]])
              [:tr {:style { :font-weight "bold" :background-color "green"}}
               [:td {:style { :width "1%" :white-space "nowrap"}} "Total:"]
               [:td (format-duration total-mins) ]]]]]))}
#+END_QUERY

I added it as a template, and now any time I need a timecard, I can easily add one.