datetime(a.tm,'localtime') time
from blob b, ancestors a
WHERE b.rid=a.rid
</pre></nowiki>


Here's a more refined variant of the above, applying what i learned during
an evening of tinkering with it...
an evening or two of tinkering with it...

<nowiki><pre>
-- All ancestors (direct or merged!) of the checkin
-- RID given in the first SELECT...
WITH RECURSIVE
-- Change this RID to the RID of the
-- origin for ancestry tracking
  theId(id) AS(
    SELECT 3285 as id -- origin RID
--    SELECT '8f89acc0f05df7bae1e7946efe5324f1e6905a9e' as id
-- Change config.rid and config.cutoffTime to change the query's behaviour.
-- rid is the starting version
-- cutoffTime is a Julian Day limit when searching for older
-- versions, based on rid's checkin time. If cutoffTime is 0
-- or less then no time limit is applied.
  config(rid,cutoffTime) AS(

-- Most recent commit:
    SELECT (SELECT MAX(objid) FROM event ORDER BY mtime DESC) rid,
-- SELECT 3285 as rid
-- Julian day limit to go back in time. <=0 means unlimited:
   1.5 cutoffTime
-- No limit:
--    0
  ),
  origin(rid, mtime, cutoffTime) AS(
  origin(rid, mtime,cutoffTime) AS(
    -- origin RID
    SELECT b.rid as rid,
           e.mtime as mtime,
           (e.mtime - 1.5) as cutoffTime -- Julian days
    FROM blob b, event e, theRid
           config.cutoffTime cutoffTime
    FROM blob b, event e, config
    WHERE
    b.rid=theId.id
    b.rid=config.rid
-- or use the UUID of the origin:
--  b.uuid=theId.id
    AND e.objid=b.rid
  ),
  ancestors(rid,uuid,tm,user,comment) AS (
     SELECT b.rid, b.uuid, e.mtime, e.user,
            coalesce(e.ecomment,e.comment)
        FROM blob b, event e, origin
        WHERE b.rid=origin.rid and e.objid=b.rid
     UNION ALL
     SELECT p.pid, b.uuid, e.mtime, e.user,
            coalesce(e.ecomment,e.comment)
     FROM plink p, blob b,
          ancestors a, event e,
          origin
        WHERE p.cid=b.rid
        AND p.cid=a.rid
        AND e.objid=p.pid
-- Only trace back this far in time...
        AND e.mtime >= origin.cutoffTime
-- Only trace back this far in time (Julian Days)...
-- Replace set config.cutoffTime to 0 to go back any amount of time
-- ^^^ if that is removed, also remove origin from the join!

-- (and be prepared to add a LIMIT unless you want a long,
-- Optionally limit it to the first N
-- ancestors (including the original checkin):
--    LIMIT 5
-- long list).
        AND CASE WHEN origin.cutoffTime>0 THEN e.mtime >= (origin.mtime - 1.5) ELSE 1 END
 )
SELECT a.rowid, b.rid,
       substr(b.uuid,0,8) uuid,
SELECT a.rid,
       substr(a.uuid,0,8) uuid,
       datetime(a.tm,'localtime') time,
       user,
       substr(comment,0,20)||'...' comment
from blob b, ancestors a
WHERE b.rid=a.rid
from ancestors a
-- Optionally limit it to the first N
-- ancestors (including the original checkin):
-- LIMIT 25
-- A config.cutoffTime time can prevent it from going too far back
;
</pre></nowiki>


<h2>Checkout-vs-repo Overview</h2>

An overview of info similar to that provided by <tt>fossil changes</tt> and similar commands: