SQL for exporting Things 3 for Mac projects - with given keyword in the project name database - into HTML that can be imported in Notion.

gistfile1.txt

Export Things 3 Tasks and Subtasks to HTML

This SQL script extracts tasks and subtasks (checklist items) from a Things 3 SQLite database.

Features:
	•	Projects grouped with their description
	•	Tasks listed under each project
	•	Completed tasks and subtasks are prepended with X
	•	Creation, due, deadline, and completion dates included in semantic <time> tags
	•	Task notes shown under tasks
	•	Checklist items listed as nested <ul>

Usage:
	1.	Open your Things 3 SQLite database.
	2.	Run the SQL script.
	3.	Export the resulting html_block field.

Output Example:
See the example output section above.

Notes:
	•	status = 3 means “completed” for both tasks and subtasks.
	•	The script uses GROUP_CONCAT to build full HTML blocks per project.


See https://culturedcode.com/things/support/articles/2982272/ for how to find the SQLite file.

Example output: 
<h2>Project A</h2>
<p>Description of project A.</p>
<ul>
  <li>X Task 1 <time datetime="2025-02-10">Completed: 2025-02-10</time>
    <ul>
      <li>
        <time datetime="2025-01-12">Created: 2025-01-12</time><br>
        <time datetime="2025-02-01">Due: 2025-02-01</time><br>
        <p>This is an important note for Task 1</p>
        <ul>
          <li>X Subtask 1 (done)</li>
          <li>Subtask 2 (not done)</li>
        </ul>
      </li>
    </ul>
  </li>
  <li>Task 2
    <ul>
      <li>
        <time datetime="2025-01-15">Created: 2025-01-15</time><br>
      </li>
    </ul>
  </li>
</ul>

WITH checklist AS (
    SELECT 
        task, -- checklist's parent TMTask uuid
        GROUP_CONCAT(
            '<li>' || 
            CASE WHEN status = 3 THEN 'X ' ELSE '' END || 
            title || 
            '</li>'
        ) AS checklist_html
    FROM 
        TMChecklistItem
    GROUP BY 
        task
)

SELECT 
    '<h2>' || p.title || '</h2>' ||
    CASE 
        WHEN p.notes IS NOT NULL AND p.notes != '' THEN
            '<p>' || p.notes || '</p>'
        ELSE ''
    END ||
    '<ul>' || 
    GROUP_CONCAT(
        '<li>' || 
            CASE WHEN t.status = 3 THEN 'X ' ELSE '' END || 
            t.title || 
            CASE 
                WHEN t.stopDate IS NOT NULL THEN 
                    ' <time datetime="' || strftime('%Y-%m-%d', t.stopDate, 'unixepoch') || '">' || 'Completed: ' || strftime('%Y-%m-%d', t.stopDate, 'unixepoch') || '</time>'
                ELSE ''
            END ||
            CASE 
                WHEN 
                    t.creationDate IS NOT NULL OR 
                    t.startDate IS NOT NULL OR 
                    t.deadline IS NOT NULL OR 
                    (t.notes IS NOT NULL AND t.notes != '') OR 
                    c.checklist_html IS NOT NULL
                THEN
                    '<ul><li>' ||
                        CASE 
                            WHEN t.creationDate IS NOT NULL THEN 
                                '<time datetime="' || strftime('%Y-%m-%d', t.creationDate, 'unixepoch') || '">Created: ' || strftime('%Y-%m-%d', t.creationDate, 'unixepoch') || '</time><br>'
                            ELSE ''
                        END ||
                        CASE 
                            WHEN t.startDate IS NOT NULL THEN 
                                '<time datetime="' || strftime('%Y-%m-%d', t.startDate, 'unixepoch') || '">Due: ' || strftime('%Y-%m-%d', t.startDate, 'unixepoch') || '</time><br>'
                            ELSE ''
                        END ||
                        CASE 
                            WHEN t.deadline IS NOT NULL THEN 
                                '<time datetime="' || strftime('%Y-%m-%d', t.deadline, 'unixepoch') || '">Deadline: ' || strftime('%Y-%m-%d', t.deadline, 'unixepoch') || '</time><br>'
                            ELSE ''
                        END ||
                        CASE 
                            WHEN t.notes IS NOT NULL AND t.notes != '' THEN
                                '<p>' || t.notes || '</p>'
                            ELSE ''
                        END ||
                        CASE 
                            WHEN c.checklist_html IS NOT NULL THEN
                                '<ul>' || c.checklist_html || '</ul>'
                            ELSE ''
                        END
                    || '</li></ul>'
                ELSE ''
            END
        || '</li>'
    , '')
    || '</ul>' AS html_block
FROM 
    TMTask t
INNER JOIN 
    TMTask p
ON 
    t.project = p.uuid
LEFT JOIN 
    checklist c
ON 
    c.task = t.uuid
WHERE 
    p.type = 1
    AND p.title LIKE '%' || :keyword || '%'
GROUP BY 
    p.uuid
ORDER BY 
    p.title ASC;