Time aggregation by month by project

Questions & Help Queries
1

Hi queries experts!

I tried hard but I was unsuccessful…Here is my problem.

I log everyday in the journal the time sent as property and other fields for my projects under this model:

2025-04-27

[[my_project_A]]
time-spent:: 45
- some notes

and in the page of my_project_A there is tags:: #project

I would like to do a query which aggregates the sum of time spent by project and by month.
the result would be
month | project | time (in mins or hours)
2025-04 | project_A | 240

I tried different ways (thanks chatgpt) but it seems that tables render only block | page columns. I basically don’t know if we are limited in term of table output columns.

for example I tried the query below, but no “No Matched results” which I don’t get.

I feel I miss something in the query paradigm…Thanks a lot in advance and thanks again for the efforts you put into this great project! I really enjoy Logseq.

#+BEGIN_QUERY
{
  :title "Monthly time spent on projects"
  :query [
    :find    ?month ?projectname (sum ?ts)
    :in      $
    :where
      [$ ?pg        :page/journal?           true]
      [$ ?pg        :page/journal-day        ?jd]
      [(str ?jd)    ?jds]
      [(subs ?jds 0 6) ?month]
      [$ ?b         :block/page             ?pg]
      [$ ?b         :block/properties       ?prop]
      [$ ?prop      :block/property         "time-spent"]
      [$ ?prop      :block/property-value   ?ts]
      [$ ?b         :block/refs             ?projectPage]
      [$ ?projectPage  :block/name             ?projectname]
      [$ ?projectPage  :block/properties       ?tprop]
      [$ ?tprop     :block/property         "tags"]
      [$ ?tprop     :block/property-value   "[[project]]"]
  ]
  :view :table
}
#+END_QUERY
2

Try this:

#+BEGIN_QUERY
{:title "Monthly time spent on projects"
 :query [:find ?year ?month ?project-name (sum ?time-spent)
   :with ?b
   :where
     [?project-tag :block/name "project"]
     [?project :block/tags ?project-tag]
     [?project :block/original-name ?project-name]
     [?b :block/refs ?project]
     [?b :block/page ?p]
     [?b :block/properties ?props]
     [(get ?props :time-spent) ?time-spent]
     [?p :page/journal-day ?date]
     [(str ?date) ?str-date]
     [(subs ?str-date 0 4) ?year]
     [(subs ?str-date 4 6) ?month]
 ]
 :result-transform (fn [results] (sort-by
   (juxt first second (fn [v] (nth v 2)))
   (partition 4 results)
 ))
 :view (fn [results]
   (for [[year month name sum] results]
     [:div year "-" month " | " [:a {:href (str "#/page/" name)} name] " | " sum]
   )
 )
}
#+END_QUERY