scien · December 2, 2016 16:05 · Sep 23, 2015 · Sep 10, 2014 · Sep 10, 2014 · Sep 10, 2014
diff --git a/gistfile1.txt b/gistfile1.txt
@@ -39,4 +39,9 @@ Find a specific message from a friend
          WHERE handle_id = 41 and ROWID >= 7005
          ORDER BY date
          LIMIT 10;
-
+
+Readable Date Format
+ SELECT ROWID, datetime(date + strftime('%s', '2001-01-01 00:00:00'), 'unixepoch', 'localtime') as date, is_from_me, text
+ FROM message
+ WHERE handle_id in (1, 27)
+ ORDER BY date;
diff --git a/gistfile1.txt b/gistfile1.txt
@@ -7,19 +7,36 @@ Helpful Commands
  sqlite> .headers off
  sqlite> .headers on
  sqlite> .mode line
+ sqlite> .mode list
+ sqlite> .schema handle
  sqlite> .schema message
 
-Find a specific friend by phone number
- sqlite> SELECT ROWID, id FROM handle WHERE id like '+1802%'; // area code 802 phone numbers
+Find a specific friend by phone number (find their handle id)
+ sqlite> /* example: look for friend's from the 802 area code */
+ sqlite> SELECT ROWID, id
+         FROM handle
+         WHERE id like '+1802%';
 
 Find message history with a specific friend
  sqlite> /*  let's assume the ROWID from the previous query was 41 */
  sqlite> .mode list
- sqlite> SELECT ROWID, date, is_from_me, text FROM message WHERE handle_id=41 ORDER BY date;
+ sqlite> SELECT ROWID, date, is_from_me, text
+         FROM message
+         WHERE handle_id=41
+         ORDER BY date;
 
 Find a specific message from a friend
- sqlite> SELECT ROWID, date, is_from_me, text FROM message WHERE handle_id=41 and text like '%something they said%' ORDER BY date;
- sqlite> /* then look at the ROWID. We'll use 7005 for this example */
+ sqlite> /* look at the ROWID of this query.  */
+ sqlite> SELECT ROWID, date, is_from_me, text
+         FROM message
+         WHERE handle_id=41 and text like '%something they said%'
+         ORDER BY date;
+
+ sqlite> /* Let's assume we found our message at ROWID=7005 */
  sqlite> /* then we can grab the next 10 messages from that conversation */
- sqlite> select ROWID, date, is_from_me, text from message where handle_id = 41 and ROWID >= 7005 ORDER BY date LIMIT 10;
+ sqlite> SELECT ROWID, date, is_from_me, text
+         FROM message 
+         WHERE handle_id = 41 and ROWID >= 7005
+         ORDER BY date
+         LIMIT 10;
 
diff --git a/gistfile1.txt b/gistfile1.txt
@@ -6,4 +6,20 @@ Helpful Commands
  sqlite> .tables
  sqlite> .headers off
  sqlite> .headers on
- sqlite> .mode line
+ sqlite> .mode line
+ sqlite> .schema message
+
+Find a specific friend by phone number
+ sqlite> SELECT ROWID, id FROM handle WHERE id like '+1802%'; // area code 802 phone numbers
+
+Find message history with a specific friend
+ sqlite> /*  let's assume the ROWID from the previous query was 41 */
+ sqlite> .mode list
+ sqlite> SELECT ROWID, date, is_from_me, text FROM message WHERE handle_id=41 ORDER BY date;
+
+Find a specific message from a friend
+ sqlite> SELECT ROWID, date, is_from_me, text FROM message WHERE handle_id=41 and text like '%something they said%' ORDER BY date;
+ sqlite> /* then look at the ROWID. We'll use 7005 for this example */
+ sqlite> /* then we can grab the next 10 messages from that conversation */
+ sqlite> select ROWID, date, is_from_me, text from message where handle_id = 41 and ROWID >= 7005 ORDER BY date LIMIT 10;
+
diff --git a/gistfile1.txt b/gistfile1.txt
@@ -0,0 +1,9 @@
+How to connect
+ > sqlite3 ~/Library/Messages/chat.db
+
+Helpful Commands
+ sqlite> .help
+ sqlite> .tables
+ sqlite> .headers off
+ sqlite> .headers on
+ sqlite> .mode line
No results found