SQL for iPhone messages

messages.sql

select 
  case is_from_me when 0 then 'Other Person' else 'Me' end, 
  -- you may be wondering what the hell 'date + 978307200' is about
  -- the iPhone stores dates as seconds past Jan 1st, 2001 rather than 1970
  -- adding the 978307200 will 'move' your date up to the epoch, which allows sqllite to convert it for you
  datetime(date + 978307200,'unixepoch', 'localtime'), 
  text 
from 
  message 
where 
  handle_id in (
    select 
      ROWID 
    from 
      handle 
    where 
      id = '<phone number you care about as +1[area code][number]>'
  )
;

pragma.sql

PRAGMA table_info(handle);

SQL for iPhone messages

SQL for iPhone messages