Converting the date within the places.sqlite file in Firefox to a DateTime

Question

I am having some trouble converting a date found in the file that stores browser history to a normal DateTime.

The file is located at: C:\Users[username]\AppData\Roaming\Mozilla\Firefox\Profiles[profilename]\places.sqlite

The table in question is: [moz_places]

The column is: [last_visit_date]

---

I've tried using the unix epoch and webkit format(like chrome uses), but neither are giving me the results I expect.

Here is my Unix conversion(not working):

    public static DateTime FromUnixTime(long unixTime)
    {
        var epoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
        return epoch.AddSeconds(unixTime);
    }

This is my webkit conversion code: (Also not working for these dates, it works with chromes webkit dates)

    public static DateTime ConvertWebkitTimeToDateTime(long ticks)
    {
        //Set up a date at the traditional starting point for unix time.
        DateTime normalDate = new DateTime(1970, 1, 1, 0, 0, 0, 0);
        //Subtract the amount of seconds from 1601 to 1970.
        long convertedTime = (ticks - 11644473600000000);
        //Devide by 1000000 to convert the remaining time to seconds.
        convertedTime = convertedTime / 1000000;
        //Add the seconds we calculated above.
        normalDate = normalDate.AddSeconds(convertedTime);
        //Finally we have the date.
        return normalDate;
    }

So what's the deal with these dates Firefox is storing? Here are a couple sample dates that should all be around today's date or yesterday's.(about 10/17/2013)

1373306583389000

1373306587125000

1373306700392000

Any help or documentation links would be awesome, thanks.

Answer 1

Unix timestamps are measured in seconds. These values are larger by a factor of one million, i.e., they are using microseconds:

> select datetime(1373306583389000 / 1000000, 'unixepoch');
2013-07-08 18:03:03

Answer 2

Solution in C#:

    public static DateTime FromUnixTime(long unixTime)
    {
        unixTime = unixTime / 1000000;
        var epoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
        return epoch.AddSeconds(unixTime);
    }

CL. was correct in that this is simply the millisecond value from the Unix epoch.

Solution in SQL:

Here is a resource that explains his solution in greater detail:

https://support.mozilla.org/en-US/questions/835204