List of recently created/modified pages (timestamp issues)

[michael]

Hello query experts,

I want to create a query that lists “recenty created” and “recently modified” pages, i.e. pages (not blocks) with created-at or modified-at during the last couple of days. I thought that this should work:

#+BEGIN_QUERY
{
:title "Recently created"
:query [
	:find (pull ?h [*])
	:in $ ?start
	:where
	[?h :block/created-at ?d]
	[(>= ?d ?start)] 
] 
:inputs [:3d]
:collapsed? false}
#+END_QUERY

However, this returns a table with all pages, not just the the ones that were created in the last three days.

So, what’s wrong with the query?

UPDATE: I now understand that :3d returns a YYYYMMDD timestamp (e.g. 20220206) whereas the :block/created-at uses UNIX timestamps (e.g. 1642588328484). That explains why the query above returns all pages this way. So, how do I get around this? Is it somehow possible to convert between the formats? Or is there a way to get the current time in UNIX format into :inputs?

[Alex_QWxleA]

This should work:

#+BEGIN_QUERY
{:title [:h2 "My 24h changes"]
:query [:find (pull ?b [* {:block/_parent ...}])
:in $ ?end
:where
[?b :block/updated-at ?v]
[(- ?end 86400000 ) ?period]
[(>= ?v ?period)]
[(< ?v ?end)]
]
:inputs [:end-of-today-ms]
}
#+END_QUERY

See logseq/query_react.cljs , which pointed to three additional helpers:

You have to choose one of these three inputs; 86400000 is 24 hours in milliseconds (60 x 60 x 24 x 1000), a week would be 604800000 milliseconds.

• :right-now-ms will give you a 24 hours period from this moment, not very useful, imho
• :start-of-today-ms which you can use for yesterday’s changes
• :end-of-today-ms, which would be more useful for today’s changes

[michael]

Hey, thank you, Alex! That works great!

One thing I would change in order to make the query more readable and easier to edit is to replace the hardcoded 86400000:

#+BEGIN_QUERY
{:title [:h3 "📄 Recently created pages" ]
:query [:find (pull ?b [* {:block/_parent ...}])
:in $ ?end ?daysback
:where
[?b :block/created-at ?v]
[(* ?daysback 60 60 24 1000) ?range]
[(- ?end ?range) ?period]
[(>= ?v ?period)]
[(< ?v ?end)]
]
:inputs [:end-of-today-ms 5]
}
#+END_QUERY

But that’s just some candy on top.

[michael]

Why does this query work in a normal block but not when I put it into my :default-queries in config.edn?

Normal block behaviour:

Journal page:

From my config.edn:

 :default-queries
 {:journals
  [{:title "🔨 DOING"
    :query [:find (pull ?h [*])
            ;;:in $ ?start ?today
            :where
            [?h :block/marker ?marker]
            [(contains? #{"NOW" "DOING"} ?marker)] 
            [?h :block/page ?p]]
            ;;[?p :block/journal? true]
            ;;[?p :block/journal-day ?d]
            ;;[(>= ?d ?start)]
            ;;[(<= ?d ?today)]]
    ;;:inputs [:14d :today]
    :breadcrumb-show? false
    :result-transform (fn [result]
                        (sort-by (fn [h]
                                   (get h :block/priority "Z")) result))
    :collapsed? false}
   {:title "📅 NEXT"
    :query [:find (pull ?h [*])
            :in $ ?start ?next
            :where
            [?h :block/marker ?marker]
            [(contains? #{"NOW" "LATER" "TODO"} ?marker)]
            [?h :block/ref-pages ?p]
            [?p :block/journal? true]
            [?p :block/journal-day ?d]
            [(> ?d ?start)]
            [(< ?d ?next)]]
    :inputs [:today :7d-after]
    :collapsed? false}
	{:title [:h2 "My 24h changes"]
	:query [:find (pull ?b [* {:block/_parent ...}])
			:in $ ?end
			:where
			[?b :block/updated-at ?v]
			[(- ?end 86400000 ) ?period]
			[(>= ?v ?period)]
			[(< ?v ?end)]
			]
	:inputs [:end-of-today-ms]
	:collapsed? false
	}
   ]}