思源笔记丨用 Query 挂件查询最近七天更新的笔记、指定某一天的笔记

最近七天创建的笔记

SELECT 
    '[' || b.content || '](siyuan://blocks/' || b.id ||')' as __01____pre__文档, 
    created as __02____datetime__创建时间,
    updated as __03____datetime__更新时间
FROM blocks as b
WHERE type = 'd'
  AND created >= strftime('%Y%m%d%H%M%S', datetime('now', '-7 days')) -- 最近七天
  AND box != '20241028223345-d9ifjv0' -- 不包含dailynote笔记
  AND id != '.root{.id}' -- 不包含当前块   
ORDER BY created DESC;

最近七天更新的笔记

SELECT 
    '[' || b.content || '](siyuan://blocks/' || b.id ||')' as __01____pre__文档, 
    created as __02____datetime__创建时间,
    updated as __03____datetime__更新时间
FROM blocks as b
WHERE type = 'd'
  AND updated >= strftime('%Y%m%d%H%M%S', datetime('now', '-7 days')) -- 最近七天
  AND box != '20241028223345-d9ifjv0' -- 不包含dailynote笔记
  AND id != '.root{.id}' -- 不包含当前块   
ORDER BY updated DESC;

指定某一天更新的笔记

SELECT 
    '[' || b.content || '](siyuan://blocks/' || b.id ||')' as __01____pre__文档, 
    created as __02____datetime__创建时间,
    updated as __03____datetime__更新时间
FROM blocks as b
WHERE type = 'd'
  AND (created BETWEEN '20241014' AND '20241015' OR updated BETWEEN '20241014' AND '20241015') -- 指定某一天
  AND box != '20241028223345-d9ifjv0' -- 不包含dailynote笔记
  AND id != '.root{.id}' -- 不包含当前块   
ORDER BY updated DESC;