Android SMS Parsing

There are two ways to skin this cat... but one is much better!

A colleague was looking for help parsing a Android mms/sms database (mmssms.db).  It was an archived sqlite file pulled from a computer hard disk drive, apparently created through by a phone/computer sync.  The main problem was that the sms table dates were in unix epoch time (with microseconds), ant there were about 2700 entries in the sms table alone.

Initially, I  wrote a command that exported the contents of the "sms" table with sqlite using "select * from sms", read each line, assigned the date data to a variable, removed the microseconds (divide by 1000) from the date data, and used "date -d @$variable" to convert the date.  Finally, I tacked on the converted date to the end of the line.  The whole thing looked like this:

$ sqlite3 -header mmssms.db "select * from sms" | while read i; do a="$(echo $i)"; b="$(echo "$i" | cut -d '|' -f5 )"; c="$(date -d @$(echo $(($b/1000)))); echo $a\|$c; done

Effective, but not very elegant.

I suspected there was a way to convert the date in sqlite, and there was.  It took me a short while to master it, but essentially, it was "select datetime(date, 'unixepoch', 'localtime') as date from sms".  That will only get you the date from each line, however, so the full command looks like this:

$ sqlite3 -header mmssms.db "select _id, thread_id, address, person, datetime(date/1000,'unixepoch','localtime') as date, protocol, read, status, type, reply_path_present, subject, body, service_center, locked from sms"

 Still a mouthful, but much faster and cleaner output.

Finally, I decided that it would be really nice to have all the tables in the database, a total of  13, exported to csv for documentation and review.  Plus, I needed a way to remember this nifty conversion trick (the same date format is found in Firefox and Chrome histories).  So I wrote a program to automate future processing. Two of the tables, sms and threads, had dates that needed to be converted, and the program makes provisions for these tables.  You can download it here.