I was contacted this morning by one of our users who wanted help decoding a date held in the database in the format 20140310111203345 found on an iPhone chat app “Nimbuzz” messenger. It’s easy to look at this and decode it by eye, i.e. 2014/03/10 11:12:03.345 but what was wanted was a way of decoding all of the dates in this form that are in a particular table.
This article will show how to format the date in a manner of our choosing for example as above or formatted in the American MM/dd/yyyy HH:mm:ss.
The process is straight forward and requires two ‘features’ of SQLite:
The first is substr a function that allows us to extract specified characters from a string (or each row in a column). The SQLite definition is:
By way of example, SubStr(date, 5, 2) extracts 2 characters from the date column at position 5 onwards and returns it as a new string.
The second is the SQLite operator Concatenate, the SQLite definition is:
The || operator is “concatenate” – it joins together the two strings of its operands.
So on to the solution. The test table we will use for this example just contains two rows as follows:
| substr(X,Y,Z) substr(X,Y) | The substr(X,Y,Z) function returns a substring of input string X that begins with the Y-th character and which is Z characters long. If Z is omitted then substr(X,Y) returns all characters through the end of the string X beginning with the Y-th. The left-most character of X is number 1. If Y is negative then the first character of the substring is found by counting from the right rather than the left. If Z is negative then the abs(Z) characters preceding the Y-th character are returned. If X is a string then characters indices refer to actual UTF-8 characters. If X is a BLOB then the indices refer to bytes. |
Our task is to create a second column with the formatted date as above, i.e. 2014/03/10 11:12:03.345.
The first query we will use will just extract the first four characters (i.e. 4 characters starting at character 1), the year, and created a new ‘virtual’ column that we will call ‘FormattedDate’. The SQL and output table is below:
Working through the complete string and also formatting the time characters gives us the final query as below:
Remember once you have created this query you can save it to the Query manager for use in a future case:
Finally, of course, we don’t have to extract the date in the order that the characters are displayed; if we wanted to use the American MM/dd/yyyy HH:mm:ss format then we can simply rearrange our query as follows:
The final display in the Forensic Browser for SQLite looks as follows:
