mail-digest

Mail Digest — Email Summary for Any Time Period

Period: $ARGUMENTS (default: today)

Step 1: Parse the time expression into a Unix timestamp range

Interpret $ARGUMENTS naturally. Map to START and END unix timestamps:

DB="$HOME/Library/Mail/V10/MailData/Envelope Index"

# Use SQLite date expressions directly — never compute Unix epochs manually (wrong year risk).
# "today"       → >= date('now','localtime')
# "yesterday"   → >= date('now','-1 day','localtime') AND < date('now','localtime')
# "last 48h"    → >= datetime('now','-48 hours')
# "last 7 days" → >= date('now','-7 days','localtime')
# Apply the range in the WHERE clause below using datetime() comparisons.

Step 2: Pull all messages in the window

sqlite3 "$DB" "
SELECT datetime(m.date_received,'unixepoch','localtime') as dt,
       s.subject, a.address as sender, a.comment as name,
       mb.url as mailbox, m.ROWID,
       m.read, m.flagged, m.is_urgent, m.size,
       m.automated_conversation, m.unsubscribe_type
FROM messages m
JOIN subjects  s  ON m.subject = s.ROWID
JOIN addresses a  ON m.sender  = a.ROWID
JOIN mailboxes mb ON m.mailbox = mb.ROWID
WHERE datetime(m.date_received,'unixepoch','localtime') >= date('now','localtime')
  -- adjust the expression above based on $ARGUMENTS
  AND m.deleted = 0
  AND mb.url NOT LIKE '%Spam%'
  AND mb.url NOT LIKE '%Trash%'
  AND mb.url NOT LIKE '%Junk%'
  AND mb.url NOT LIKE '%Draft%'
  AND mb.url NOT LIKE '%Sent%'
ORDER BY m.is_urgent DESC, m.date_received DESC;" 2>/dev/null