WARNING! Make a copy of your database before trying any of this!
Why do it?
I have been using the Day One journaling app to keep a daily personal diary for many years now. I have been recording my mundane thoughts there for over seven years and have transcribed five paper diaries too giving me a total of 14 year held there.
That’s currently 4,267 entries and counting of which I know very little about other than the text itself. I wanted more detail than Day One offered and had ideas of how I could mine my entries in cool ways (more on that in a later post).
A quick search turned up that the data is held in a SQLite database which I thought could be a way to get to do some of the things I wanted.
How to do it
Obviously (or maybe not) you can only do this on a desktop device and not a mobile so MacOS only. You can find where your data is held in this useful article from Day One.
As I said at the start please, please, please make a copy of the SQLite file. Even though the queries shown here made on the database are read only I’d still hate you to accidently screw up your entries! In my case the file needed was here:
/Users/neilthompson/Library/Group Containers/5U8NS4GX82.dayoneapp2/Data/Documents/DayOne.sqliteTo access the database I used the excellent Beekeeper Studio but you can obviously use your database tool of choice providing it supports SQLite. And, of course, you can access it from your favourite development language, again, more of which in a future post.
Database Structure
On opening the database you will see that there are 61 tables and I have no idea what most of them do. However, some of them are very obvious and I concentrated on only these three tables to get the information I wanted: ZENTRY, ZLOCATION and ZWEATHER.
The ZENTRY table is the one that holds all your text entries – there aren’t any images stored in the database just reference to them as they are held either on disk, in the cloud or both. ZWEATHER and ZLOCATION do exactly what they say on the tin.
Most of the tables and columns are pretty straightforward but I came a bit unstuck with the ZCREATIONDATE column in the ZENTRY table. This is a timestamp and I assumed that it would be a typical Unix timestamp starting from 1970-01-01. However, whenever I did the conversation the dates were not what I expected at all. After a bit of investigation I found that the dates were exactly 31 years out which led me to this StackOverflow entry. It turns out that Apple dates start from 2001-01-01 – I have no idea why but once I had this piece of information I was able to unlock the dates.
Sample Queries
The following are some sample queries that I wrote to get more statistics from the database. Most have sample output so you can see what you are getting. What would be a cool project would be a “live” dashboard showing this information and I will look to do this at a later date.
Some of these queries rely on you having given Day One permission to capture certain information, such as location. If you haven’t done so then I guess these columns will be blank.
I hope that you find these useful, they should work straight out of the box, but they are based on what is in my database so your mileage may vary of course.
Top Ten Countries Entries were written
This is a high-level look at in which countries I created my entries. Whether this is useful to you or not will depend on how much you travel but it does show how the location and entry tables work together.
SELECT l.ZCOUNTRY AS 'Country', COUNT(*) AS 'Count'
FROM ZLOCATION l, ZENTRY e
WHERE e.ZLOCATION = l.Z_PK
AND l.ZCOUNTRY IS NOT NULL
GROUP BY l.ZCOUNTRY
ORDER BY COUNT(*) DESC LIMIT 10
Top Ten Places Entries were written
The database has a much finer level of location, usually right down to the house number and street name which is held in the ZPLACENAME column. However, what I found was that the precision wasn’t that great and so many entries looked as if they had been created in my neighbours houses. To get round this I stripped off the number that preceded the street name and used this.
I haven’t shown the output from this as I don’t want to dox myself!
SELECT LTRIM(l.ZPLACENAME, '0123456789 –_') || ', ' || l.ZCOUNTRY AS 'Placename', COUNT(*) AS 'Count'
FROM ZLOCATION l, ZENTRY e
WHERE e.ZLOCATION = l.Z_PK
GROUP BY PLACENAME
ORDER BY COUNT(*) DESC LIMIT 10Time of Day Entries were written
For the reasons I explained above this query took the longest to work out and also accounts for the strange datetime command which converts the Day One date to a localtime after adding the 31 years.
The output from this is pretty much as expected – I write my entries at the end of the day just prior to going to bed but there are some outlyers including a couple written at 3 am!
SELECT strftime('%H', datetime(e.ZCREATIONDATE, 'unixepoch', '31 years', 'localtime')) AS 'hour', COUNT(*) AS 'Count'
FROM ZENTRY e, ZLOCATION l
WHERE e.ZLOCATION = l.Z_PK
AND l.ZCOUNTRY = 'United Kingdom'
GROUP BY strftime('%H', datetime(e.ZCREATIONDATE, 'unixepoch', '31 years', 'localtime'))
ORDER BY strftime('%H', datetime(e.ZCREATIONDATE, 'unixepoch', '31 years', 'localtime'))
What device type were the entries created on
Another interesting factoid that is recorded in the entry table is the device that you created the entry on. If you are on the free plan then this will only be a single device but as I have a paid account I have used on multiple devices. I was actually surprised at just how many I had written on my iPhone as this is not the easiest way to bash out large blocks of text.
SELECT e.ZCREATIONDEVICETYPE AS 'Device', COUNT(*) AS 'Count'
FROM ZENTRY e
WHERE e.ZCREATIONDEVICETYPE IS NOT NULL
GROUP BY e.ZCREATIONDEVICETYPE
ORDER BY COUNT(*) DESC LIMIT 10
Top 10 weather in home country
The final example is using another piece of information that is stored with each entry – what the weather was like in the place where you were making that entry. I’m not exactly sure where this comes from except that release notes from December ’22 stated “Apple Weather is now the default weather service for devices that have been updated to iOS 16” so I guess it must have been Dark Sky before that.
What’s great about this for me is that it shows that here in England it isn’t always raining! In fact only 263 times out of a total of 3,162 (8.3%) was there any rain. And if you are wondering why that total (3,162) doesn’t match the total number of entries (4,267) that’s because some weather entries were null and these have been excluded.
SELECT w.ZCONDITIONSDESCRIPTION AS 'Weather', COUNT(*) AS 'Count'
FROM ZWEATHER w, ZLOCATION l, ZENTRY e
WHERE w.ZCONDITIONSDESCRIPTION IS NOT NULL
AND e.ZLOCATION = l.Z_PK
AND e.ZWEATHER = w.Z_PK
AND l.ZCOUNTRY = 'United Kingdom'
GROUP BY w.ZCONDITIONSDESCRIPTION
ORDER BY COUNT(*) DESC LIMIT 10