Introduction

Alfred’s copy history is by far one of its most powerful features. It helps you store strings, images, etc for quite some time (Up to 3 Months 😱).

But if you suddenly remember about that short message containing an account number you cannot find, going back in history can be quite hard.

Requirements: DB IDE to open and query SQLite DB. Dbeaver, or SQLite are both good enough.

Alfred advanced search

Alfred’s DB is stored at ~/Library/Application Support/Alfred/Database, connect to that database with your tool of preference.

Alfred stores it’s timestamp in the ts column this value is in seconds since reference date (00:00:00 UTC on 1 January 2001).

However unix timestamps are all since January 1st 1970, so we will be adding the time difference to the timestamp and creating a convenient VIEW to query the history

978303600 seconds elapsed between epoch and 2001. In my case, I’m adding some hours to the date to match take my timezone into account, which will help me match exactly with what Alfred displays.

create view clipboard_date as
select item, datetime(datetime(ts + 978303600, 'unixepoch'), '+3 hours') as datetime, *
from clipboard c;

Once the view is created, we can directly query our history by several attributes which comes in handy. Let’s search for a love message that I never sent to my beloved girlfriend.

I remember being in pain, love had abandoned me around mid July, when spring had just left us and summer led the way to lovely bikinis, and walking muscles on the beach. Skinny as I am, I stood no chance against that handsome tall bastard.

select *
from clipboard_date cd 
where datetime < '2022-07-15 00:00:00'
and app = "Google Chrome"
and length(item) > 50
and item like '%I miss you so much%'
order by datetime desc

There you are! 🙌 We found it 😍

“Honey, I know I will never have Jonny’s strength or torso, just in the same way as your body will be plunged by gravity as we grow old. Since I miss you so much, I found myself another girlfriend that at least knows how to use her brain. Good luck with that dickhead”