分享一个极简任务追踪和回顾的 Query

-- 查询72h更新的文档
SELECT
    '[' || b.hpath || '](siyuan://blocks/' || b.id || ')' AS __1____pre__文档路径,
    CASE
        WHEN b.updated != '' THEN b.updated
        ELSE b.created
    END AS __2____datetime__最后更新时间,
    b.created AS __3____datetime__创建时间,
    printf('%d天%d小时%d分钟%d秒',
        ABS(
            (CAST(SUBSTR(b.updated, 1, 4) AS INTEGER) - CAST(SUBSTR(b.created, 1, 4) AS INTEGER)) * 365 * 24 * 60 * 60 + 
            (CAST(SUBSTR(b.updated, 5, 2) AS INTEGER) - CAST(SUBSTR(b.created, 5, 2) AS INTEGER)) * 30 * 24 * 60 * 60 + 
            (CAST(SUBSTR(b.updated, 7, 2) AS INTEGER) - CAST(SUBSTR(b.created, 7, 2) AS INTEGER)) * 24 * 60 * 60 + 
            (CAST(SUBSTR(b.updated,9, 2) AS INTEGER) - CAST(SUBSTR(b.created, 9, 2) AS INTEGER)) * 60 * 60 + 
            (CAST(SUBSTR(b.updated, 11, 2) AS INTEGER) - CAST(SUBSTR(b.created, 11, 2) AS INTEGER)) * 60 + 
            (CAST(SUBSTR(b.updated, 13, 2) AS INTEGER) - CAST(SUBSTR(b.created, 13, 2) AS INTEGER))
        ) / 86400, -- 天
        ABS(
            (CAST(SUBSTR(b.updated, 1, 4) AS INTEGER) - CAST(SUBSTR(b.created, 1, 4) AS INTEGER)) * 365 * 24 * 60 * 60 + 
            (CAST(SUBSTR(b.updated, 5, 2) AS INTEGER) - CAST(SUBSTR(b.created, 5, 2) AS INTEGER)) * 30 * 24 * 60 * 60 + 
            (CAST(SUBSTR(b.updated, 7, 2) AS INTEGER) - CAST(SUBSTR(b.created, 7, 2) AS INTEGER)) * 24 * 60 * 60 + 
            (CAST(SUBSTR(b.updated, 9, 2) AS INTEGER) - CAST(SUBSTR(b.created, 9, 2) AS INTEGER)) * 60 * 60 + 
            (CAST(SUBSTR(b.updated, 11, 2) AS INTEGER) - CAST(SUBSTR(b.created, 11, 2) AS INTEGER)) * 60 + 
            (CAST(SUBSTR(b.updated, 13, 2) AS INTEGER) - CAST(SUBSTR(b.created, 13, 2) AS INTEGER))
        ) % 86400 / 3600, -- 小时
        ABS(
            (CAST(SUBSTR(b.updated, 1, 4) AS INTEGER) - CAST(SUBSTR(b.created, 1, 4) AS INTEGER)) * 365 * 24 * 60 * 60 + 
            (CAST(SUBSTR(b.updated, 5, 2) AS INTEGER) - CAST(SUBSTR(b.created, 5, 2) AS INTEGER)) * 30 * 24 * 60 * 60 + 
            (CAST(SUBSTR(b.updated, 7, 2) AS INTEGER) - CAST(SUBSTR(b.created, 7, 2) AS INTEGER)) * 24 * 60 * 60 + 
            (CAST(SUBSTR(b.updated, 9, 2) AS INTEGER) - CAST(SUBSTR(b.created, 9, 2) AS INTEGER)) * 60 * 60 + 
            (CAST(SUBSTR(b.updated, 11, 2) AS INTEGER) - CAST(SUBSTR(b.created, 11, 2) AS INTEGER)) * 60 + 
            (CAST(SUBSTR(b.updated, 13, 2) AS INTEGER) - CAST(SUBSTR(b.created, 13, 2) AS INTEGER))
        ) % 3600 / 60, -- 分钟
        ABS(
            (CAST(SUBSTR(b.updated, 1, 4) AS INTEGER) - CAST(SUBSTR(b.created, 1, 4) AS INTEGER)) * 365 * 24 * 60 * 60 + 
            (CAST(SUBSTR(b.updated, 5, 2) AS INTEGER) - CAST(SUBSTR(b.created, 5, 2) AS INTEGER)) * 30 * 24 * 60 * 60 + 
            (CAST(SUBSTR(b.updated, 7, 2) AS INTEGER) - CAST(SUBSTR(b.created, 7, 2) AS INTEGER)) * 24 * 60 * 60 + 
            (CAST(SUBSTR(b.updated, 9, 2) AS INTEGER) - CAST(SUBSTR(b.created, 9, 2) AS INTEGER)) * 60 * 60 + 
            (CAST(SUBSTR(b.updated, 11, 2) AS INTEGER) - CAST(SUBSTR(b.created, 11, 2) AS INTEGER)) * 60 + 
            (CAST(SUBSTR(b.updated, 13, 2) AS INTEGER) - CAST(SUBSTR(b.created, 13, 2) AS INTEGER))
        ) % 60 -- 秒
    ) AS __4____pre__间隔时间
FROM
    blocks AS b
WHERE
    b.type = 'd'
    AND b.root_id != '.block{.root_id}'
    AND (
        b.updated > strftime(
            '%Y%m%d%H%M%S',
            'now',
            'localtime',
            '-3 day'
        )
        OR b.created > strftime(
            '%Y%m%d%H%M%S',
            'now',
            'localtime',
            '-3 day'
        )
    )
ORDER BY
    __2____datetime__最后更新时间 DESC
LIMIT -1;
​