Using SQL as a date/time conversion tool

A few weeks ago I spent a little time looking at Triggers and to get to know them better I wanted to make use of them and so I knocked up a small application where if an integer is pasted into a cell in a table (the base cell in the screen shot below) then this integer is then converted automatically into different date formats – essentially a time conversion tool, similar to Decode or Time-lord but built from SQL. Like the name implies a trigger is activated when certain events take place, a row is inserted or deleted or a specific cell is modified etc.

The article shows how to create these triggers and there is a link to the database itself (although it is extremely simple to make your own and extend it for other formats) that you can download if you don’t want to follow the steps below to get started.

Although the article seems a little trivial there is as always a forensic value to the text, it is only by examining the complete database that we can determine how it works, triggers might not be relevant for most investigations but understanding how triggers work may one day help you to understand how a certain row appeared in a table at a particular time.

First what am I trying to achieve? Basically I want a table where I can insert an integer date and have all of the possible valid dates in different possible formats (Unix, NSDate, Chrome, FileTime etc.) calculated and displayed alongside. Sound simple?

This is what it should look like after a valid NSDate has been added to the base column:


Get The Latest DFIR News

Join the Forensic Focus newsletter for the best DFIR articles in your inbox every month.


Unsubscribe any time. We respect your privacy - read our privacy policy.

The first thing to do is to create a table with a column for our “base” date and then additional columns for each of the date formats that we support, the CREATE statement is below:

CREATE TABLE dates (base, unix10 text, unix13 text, NSDate text, chrome text, filetime text)

The base column has no defined type – we could be looking at integers, floating point numbers or text. This actually is not really important with SQLite as it is pretty lax with column affinity, i.e. you can write a text value to an integer or float field, or a float to an integer…

The next thing to do is create a series of triggers. Triggers are actions that take place when certain things change. So for instance you could create a log table that had a date and time entry every time another table changed or you could recalculate the total of all of the values in a column when one of them is updated.

Our first trigger is a simple one basically before a new row is inserted in our table I want to delete every row in the table. This ensures that our table only ever has one row, this keeps things simple.

More information about SQLite triggers can be found here:

https://www.sqlite.org/lang_createtrigger.html

The trigger is as follows

CREATE TRIGGER trig_del before insert on dates 
begin 
delete from dates;
end

The first line defines when the trigger activates, in this case before a new INSERT takes place on our dates table. The line(s) bracketed by begin and end define what our trigger does, in this case deletes all rows from the dates table.

Before we move on to the rest of our triggers we’ll digress and look briefly at the SQLite DateTime function.

In simple terms this converts a number into a string depicting the date, all we need to do is provide the number and tell the function what the number represents (Unix date or julian date). You can do more such as controlling the format of the resulting text string, but this is beyond the scope of this article.

The DateTime function is detailed here:

https://www.sqlite.org/lang_datefunc.html

So to convert 1234567890 into a text string we use

DateTime(1234567890, 'unixepoch') and we get 2009-02-13 23:31:30

if we wanted to update a particular column (unix10) with this value the SQL is

UPDATE dates SET unix10 = DateTime(1234567890, 'unixepoch')

But, we need to take our unix date value form the new ly entered base column. The trigger functionality of SQLite adds a qualifier to allow us to access the old value of a column (before it was updated) or the new value (after it was updated), so to get the new value of the base column to replace our hard coded unix date we use new.base

So now to our first trigger that does some of the real work

CREATE TRIGGER trig_ins AFTER INSERT ON dates 
BEGIN
UPDATE dates SET unix10 = (DateTime(new.base, 'unixepoch')); 
END

This trigger activates after a new row has been inserted and simply calls the built IN SQLite function DateTime and passes it the new value from the base column and converts it to a text string assuming the value in base is a 10 digit unix value.

If you have read the page on triggers you will know that you can have multiple statements within the begin and end block so we can further update our insert trigger with additional statements to deal with additional date and time formats.

For unix dates expressed as 13 digit integers recording the date to millisecond accuracy we can divide the date by 1000 to give us seconds rather than milliseconds and use:

DateTime(new.base/1000, 'unixepoch')

For Google Chrome dates (Microseconds since 1/1/1601) we need to also convert this to a UnixDate (seconds since 1/1/1970). The first thing we can do is to convert from Microseconds to seconds, we simple divide the date by 1,000,000. We can then adjust for the difference in seconds, but what is this?

SQLite helpfully provides a way to do this too, we use strftime. The strftime function calculates the difference between two times and SQLite also provides us with a built in constant (%s) for the unix epoch (1/1/1970), so

strftime('%s', '1601-01-01 00:00:00')

Gives us the difference between 1/1/1970 and 1/1/1601 – neat eh? Our query now becomes

Code:
datetime((old.base/1000000)+strftime('%s', '1601-01-01 00:00:00'), 'unixepoch')

(We add the difference because stftime returns a negative number)

Now Microsoft filetimes can be dealt with in exactly the same way. Filetime is the number of 100 nano seconds since 1/1/1601 so we convert to seconds by dividing by 10,000,000 and then using the same equation as for Chrome time.

datetime((old.base/100000000000)+strftime('%s', '1601-01-01 00:00:00'), 'unixepoch')

So now what about NSDate this is the number of seconds since 1/1/2001 sometimes expressed as a floating point value. As we mentioned above SQLite doesn’t really care what values we write to a column and if we write a floating point value, then it will be stored as a float but our trigger will just convert it to an integer for us. so we can simply use:

datetime((new.base)+strftime('%s', '2001-01-01 00:00:00'), 'unixepoch')

If you have read the triggers web page you will know that we can have multiple statements within a trigger, so we can combine all of the above, as below:

CREATE TRIGGER trig_ins after insert on dates begin 
update dates set unix10 = datetime(new.base, 'unixepoch'); 
update dates set unix13 = datetime((new.base/1000), 'unixepoch'); 
update dates set chrome = datetime((new.base/1000000)+strftime('%s', '1601-01-01 00:00:00'), 'unixepoch');
update dates set filetime = datetime((new.base/10000000)+strftime('%s', '1601-01-01 00:00:00'), 'unixepoch');
update dates set nsdate = datetime((new.base)+strftime('%s', '2001-01-01 00:00:00'), 'unixepoch');
end

There is one last convenience trigger we can add. I have been using the excellent SQLite manager plugin for Firefox to create my tables and indexes as well as insert new rows to test my statements – The Forensic Browser for SQLite is not meant to have this functionality. I found though that rather than add a new row, I was simply editing the current (and only) row. So we need to add a new trigger that fires after just the base column in the table changes.

The trigger is the same as above, with just the change to the first line. I hope these need no further explanation.

CREATE TRIGGER trig_upd after update of base on dates 
begin 
update dates set unix10 = datetime(new.base, 'unixepoch'); 
update dates set unix13 = datetime((new.base/1000), 'unixepoch'); 
update dates set chrome = datetime((new.base/1000000) + strftime('%s', '1601-01-01 00:00:00'), 'unixepoch');
update dates set filetime = datetime((new.base/10000000) +strftime('%s', '1601-01-01 00:00:00'), 'unixepoch');
update dates set nsdate = datetime((new.base) + strftime('%s', '2001-01-01 00:00:00'), 'unixepoch');
end

If you don’t want to cut and paste the SQL above to create your own table (although I would encourage it) you can download the table I created using the above commands at the link below.

It should be pretty straight forward to add new date formats but if you do have trouble with a particular format then please feel free to get in touch.

datesandtimes.sqlite

To use the database above with the Firefox SQLite manager plugin. Open the file and select the dates table. Add a new row (using the add button) or edit the existing row if present and change the value in the base column to any date value you have. The database will update the remaining columns and populate them with valid dates where possible.

A screenshot of the resulting table after a valid Chrome date has been posted is below:

SELECT * FROM sqlite_master WHERE type = 'trigger'      
or just

SELECT * FROM sqlite_master

sqlite_master

Sanderson Forensics write software for the digital forensic community as well as provide consultancy services. Our software includes the Forensic Toolkit for  SQLite which provides features for creating complex SQL queries and forensic reports on any SQLite database using a drag and drop query builder as well as tools to carve records from disk/phone images and to drill down into the structure of an SQLite database/journal to understand exactly how they work.

Paul can be contacted at paul@sandersonforensics.com or via his web site at www.sandersonforensics.com

This article is an updated verison of the article that appeared at Using SQL as a date/time conversion tool.

Leave a Comment

Latest Videos

Latest Articles