How to query block property with a date?

Questions & Help
1

Hey everyone! So I have this data:

- DONE completed today
  completed:: [[Oct 22nd, 2022]]
- DONE completed yesterday
  completed:: [[Oct 21st, 2022]]
- DONE completed 10 days ago
  completed:: [[Oct 12th, 2022]]

And I want to query it for date ranges and I failed. Here is a simplified query for fetching blocks with completed property older than today:

#+BEGIN_QUERY
  {
    :title "Tasks completed yesterday"
    :query [
        :find (pull ?b [*])
        :where
            [?b :block/properties ?properties]
            [(get ?properties :completed) ?completed]
            [(< ?completed :today)]
    ]
  }
  #+END_QUERY

Today is Oct 22th, but it returns all 3 blocks, but should return one. Frankly speaking no matter what I put instead of :today (like 1d, -10d) it always returns all blocks.

I’ve tried to represent date how it’s done for built-in task’s property SCHEDULED (i.e. “<2022-10-20>”) both in property itself and in the query, but with no luck. I’m starting to give up :frowning:

Also surprisingly explicit equality doesn’t work as well (i.e. [(= ?completed "[[Oct 22nd, 2022]]" )]. Likely because underneath string cannot be casted to page-ref.

So:

  1. Can someone please help to fix a query?
  2. How to query date range? Like from 2022-10-10 to 2022-10-20?
2 Likes
2

I’m also interested in this subject, therefor waiting on any comment related.

3

For now I came up with ugly solution with simplified query language. I looked at sources how it works — logseq/query_dsl.cljs at 4c3503ff1e9732a96a605a657bb9cab3ec7b1c90 · logseq/logseq · GitHub

Solution for now:

{{query (or (property completed [[Oct 21st, 2022]]) (property completed [[Oct 20th, 2022]]) (property completed [[Oct 19th, 2022]]) (property completed [[Oct 18th, 2022]]) (property completed [[Oct 17th, 2022]]) (property completed [[Oct 16th, 2022]]) (property completed [[Oct 15th, 2022]])) }}
4

CC @Didac look at comment above

1 Like
5

I too would like to understand this. Is there a good Logseq ref that describes how date time is used. Seems that date queries don’t work in blocks and almost seems like they are being treated at text and not date fields. Appreciate any that understands Logseq internals better to explain. Thanks in advance.

1 Like
6

Hello,

The problem is that journal pages names are not real dates, yet they have the property :block/journal-day which is a date we can use.

Here is how we can do what you’re looking for :

{
    :title "Tasks completed before today"
    :inputs [:today] ; inject the current date
    :query [
        :find (pull ?b [*])
        :in $ ?that-day ; name the variables injected with :inputs
        :where
            [?b :block/properties ?properties]
            [(get ?properties :completed) ?completed-at]
            [?b :block/ref-pages ?p]      ; find the page references
            [?p :block/journal? true]      ; which are journal pages
            [?p :block/journal-day ?d]   ; extract their date
            [(< ?d ?that-day)]                ; which should be older than the date passed with :inputs
      ]
  }

:today needs to be injected because it is a dynamic variable (changing every day) so you need to add the lines :inputs [:today] and :in $ ?that-day (?that-day is the name of the variable holding the value of :today)

Then like you were doing, look at the blocks that have a :completed property and then find all the pages that those blocks are referencing. Look at the :block/journal-day of those pages and compare them with the date you injected through :inputs.

Note that this solution is not really good because if you reference another (past) journal page in your block it will be in the results.
(I tried without success to parse the value of ?completed-at to use that value instead of the :block/ref-pages)

Hope it helps :hugs:

4 Likes
7

To get journal-day from date property, use the following query

#+BEGIN_QUERY
{
 :query [:find (pull ?b [*])
         :in $ ?today
         :where
              [?b :block/properties ?properties]
              [(get ?properties :completed) ?d]
              [?j :block/journal? true]
              [?j :block/original-name ?jn]
              [(contains? ?d ?jn)]
              [?j :block/journal-day ?d-num]
              [(< ?d-num ?today)]
 ]
 :inputs [:today]
}
#+END_QUERY```
1 Like
8

I have also been playing with this, and believe I came up with a way to show the past 7 days only, dynamically based on the today’s date and journal-day values. Unfortunately, the query does not seem to update automatically yet (see discussion on https://github.com/logseq/logseq/issues/9802 )

Here is my version of the advanced query:

#+BEGIN_QUERY
{:title "Tasks completed in last 7 days"
    :query [:find (pull ?b [*] )
         :in $ ?start ?end
         :where
              [?b :block/properties ?properties]
              [(get ?properties :completed) ?d]
              [?p :page/journal? true]
              [?p :page/journal-day ?dnum]
              [?p :page/original-name ?jn]
              [(>= ?dnum ?start)]
              [(<= ?dnum ?end)]
              [(contains? ?d ?jn)]
 ]
 :inputs [:-7d :today]
}
#+END_QUERY