Database schema of Things

Software
1

I’ve recently made an automation to create calendar events based on the tasks in my Things app.

However I ran into an issue. We have this sqlite3 local database.

~/Library/Group\ Containers/JLMPQHK86H.com.culturedcode.ThingsMac/Things\ Database.thingsdatabase/main.sqlite

I believe almost everything is stored there. Does anyone know how to select the tasks in “Today” bucket?

I thought startDate was the one I needed.

The simplified version of the query I’ve been using is:

SELECT *
  FROM TMTask
WHERE startDate = ${timestamp} AND 
      startBucket = 0 AND 
      status = 0 AND 
      trashed = 0

and the timestamp is calculated with JS like this

const today = new Date();
const beginningOfToday = new Date(today.getFullYear(), today.getMonth(), today.getDate());
const timestamp = (beginningOfToday.getTime() - beginningOfToday.getTimezoneOffset()*60*1000)/1000;

But I realized my understanding of the database schema was wrong. That way it only showed me the tasks added to “Today” for the first time today. If a task was added to “Today” yesterday and I couldn’t finish it then, but it’s still in Today now, but this query won’t fetch it, because the startDate of the task is yesterday.

What else column should I use?

2

Have you tried selecting all tasks that have a start date smaller then tomorrow?
Although I have no experience with the specific database scheme (clever idea, btw) that should work from what you describe.

If the column ignores the time you could also use

SELECT *
  FROM TMTask
WHERE startDate <= ${timestamp} AND 
      startBucket = 0 AND 
      status = 0 AND 
      trashed = 0
3

This is from the things.sh cli

1 Like
4

Sorry I missed the notifications. Thanks for the replies! :slight_smile: