select mychatfield, datetime(mytimefield/1000, 'unixepoch', 'localtime') from mytable
Detailed answer:
Most SQLite databases used in Android applications store their time stamps in UNIX time, also know as UNIX Epoch. Unix time defines a point in time as "the number of seconds that have elapsed since 00:00:00 Coordinated Universasl Time (UTC), Thursday, 1 January 1970, minus the number of leap seconds that have taken place since then." It goes without saying that these date are not stored in a way that makes them understandable to our consumers.
If your forensic tool does not parse a particular SQLite DB content automatically here is a quick way of converting those UNIX time stamps to local time.
For this example I will use an Tumblr Android SQLite DB that was extracted using Magnet Forensics Acquire and FTK Imager. To view the contents I used DB Browser for SQLite. The path location of the extracted database in my exemplar phone was:
userdata (ExtX)/Root/data/com.tumblr/databases/Tumblr.sqlite
Here is how the table looks with some sample data using DB Browser for SQLite.
Notice the timestamp field. A long string of numbers. Also notice the text field. The user content we are looking for if we are interested in chat content, for example. In order to present the chats with the corresponding time stamps in a human readable way we can use a SQL query to make the conversion.
Here is the query and the results:
select text as messages, datetime(timestamp/1000, 'unixepoch', 'localtime') as dates from messaging_message
The datetime function takes three arguments in this example. The time string, UNIX epoch and local time modifiers. The reason the timestamp field values are divided by 1000 is due to the UNIX time being stored in milliseconds when the datetime function expects the UNIX time to be in seconds. By dividing we change the milliseconds to seconds.
In order to make the final column headers more descriptive change them in the query by using 'myfielname as newname' as seen above.
As always when doing these types of conversions on a case run validation tests with known data in a replica of the environment you are analyzing.
