Using SQL as a date/time conversion tool

by | Jan 7, 2015 | Forensic Browser for SQLite

In a slight aside from my recent articles re using the Forensic Toolkit for SQLite I have put together a short tutorial on using SQL to convert dates. This article came about as I was using third party utilities (and in some case SQLite) to validate the date and time conversion procedures in The Forensic Browser for SQLite.

First what am I trying to achieve? Basically I want a table where I can insert a 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:

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:

Code:

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

Code:

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

Code:

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

Code:

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

But, we need to take our Unix date value form the newly 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

Code:

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:

Code:

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 simply 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

Code:

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 as stftime will return a negative number)

Now Microsoft filetimes can be dealt with in exactly the same way. Filetime is the number of 100 nanoseconds 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.

Code:

datetime((old.base/100000000000)+strftime('%s', '1601-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:

Code:

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.

Code:

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: