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.
|For corporate or law enforcement investigators a fully functional licence for the Forensic Toolkit for SQLite can be obtained by clicking here and providing your official email address, full name and position within your organisation|
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:
|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.|
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:
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 fpor 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 re arrange our query as follows:
The final display in the Forensic Browser for SQLite looks as follows: