Query files modified in the past 12 hours

How To
1

I wonder if someone can correct the query I pieced together to generate a list of titles from files that were modified in the past 12 hours. Here is the query:

(org-roam-db-query [:select [title] :from files :where (time-less-p (time-add (current-time) -43200) mtime)])

which generates the following error:

Debugger entered--Lisp error: (emacsql-error "near \"ADD\": syntax error")
  signal(emacsql-error ("near \"ADD\": syntax error"))
  #f(compiled-function (_ code message) "Get condition for MESSAGE provided from SQLite." #<bytecode 0x467580f3>)(#<emacsql-sqlite-connection emacsql-sqlite-connection-4aa54b74> 1 "near \"ADD\": syntax error")
  apply(#f(compiled-function (_ code message) "Get condition for MESSAGE provided from SQLite." #<bytecode 0x467580f3>) #<emacsql-sqlite-connection emacsql-sqlite-connection-4aa54b74> (1 "near \"ADD\": syntax error"))
  emacsql-handle(#<emacsql-sqlite-connection emacsql-sqlite-connection-4aa54b74> 1 "near \"ADD\": syntax error")
  #f(compiled-function (connection) "Parse well-formed output into an s-expression." #<bytecode 0x466d329d>)(#<emacsql-sqlite-connection emacsql-sqlite-connection-4aa54b74>)
  apply(#f(compiled-function (connection) "Parse well-formed output into an s-expression." #<bytecode 0x466d329d>) #<emacsql-sqlite-connection emacsql-sqlite-connection-4aa54b74> nil)
  emacsql-parse(#<emacsql-sqlite-connection emacsql-sqlite-connection-4aa54b74>)
  #f(compiled-function (connection sql &rest args) "Send SQL s-expression to CONNECTION and return the results." #<bytecode 0x467450d7>)(#<emacsql-sqlite-connection emacsql-sqlite-connection-4aa54b74> [:select [title] :from files :where (time-less-p (time-add (current-time) -43200) mtime)])
  apply(#f(compiled-function (connection sql &rest args) "Send SQL s-expression to CONNECTION and return the results." #<bytecode 0x467450d7>) #<emacsql-sqlite-connection emacsql-sqlite-connection-4aa54b74> [:select [title] :from files :where (time-less-p (time-add (current-time) -43200) mtime)])
  emacsql(#<emacsql-sqlite-connection emacsql-sqlite-connection-4aa54b74> [:select [title] :from files :where (time-less-p (time-add (current-time) -43200) mtime)])
  apply(emacsql #<emacsql-sqlite-connection emacsql-sqlite-connection-4aa54b74> [:select [title] :from files :where (time-less-p (time-add (current-time) -43200) mtime)] nil)
  org-roam-db-query([:select [title] :from files :where (time-less-p (time-add (current-time) -43200) mtime)])
  eval((org-roam-db-query [:select [title] :from files :where (time-less-p (time-add (current-time) -43200) mtime)]) nil)
  elisp--eval-last-sexp(nil)
  eval-last-sexp(nil)
  funcall-interactively(eval-last-sexp nil)
  call-interactively(eval-last-sexp nil nil)
  command-execute(eval-last-sexp)
2

I think the problem is that you cannot run arbitrary elisp code inside of an sql query. So you need to filter the list returned by the sql query eg.

(mapcar #'car (cl-remove-if-not
              (lambda (it) (time-less-p (time-add nil -43200) (cadr it)))
              (org-roam-db-query [:select [title, mtime] :from files])))
1 Like
3

thanks! This solves the problem.