Thunderbird Get Sent Message Count By Week

These instructions show how to get the message count by year and week number from Thunderbird.

• Download and install a SQLite browser tool -- I recommend Sqlite Expert - Freeware
• Start Sqlite Expert
• Open the global-message-db.sqlite database file from your Thunderbird profile directory
• Look in the 'folders' table for the ID of the folder you want to search
• Run the query below, specifying the folderID you found.
• Select all the results and copy to clipboard.
• Paste into OpenOffice Calc and create a graph

Query

select
       strftime('%Y-%W', datetime(date/1000000, 'unixepoch')) AS 'yearWeek'      
      ,count(*) AS countMessages
      ,strftime('%Y', datetime(date/1000000, 'unixepoch')) AS 'year'      
      ,strftime('%W', datetime(date/1000000, 'unixepoch')) AS 'week'
from messages where folderID=119
group by
       strftime('%Y', datetime(date/1000000, 'unixepoch'))      
      ,strftime('%W', datetime(date/1000000, 'unixepoch'))

Notes

The date field is microseconds since the UNIX epoch, so it is basically a unix timestamp multiplied by 1,000,000.

The global-message-db.sqlite file contains data about your email for fast querying and searching.

Helpful link: Thunderbird kung-fu: querying the index for messages.

---