Google chrome history sqlite

Question

The data is from Chrome Google history file.

I want to know the meaning of columns in tables. So I find one table called visits in the history is:

CREATE TABLE visits(id INTEGER PRIMARY KEY,url INTEGER NOT NULL,visit_time INTEGER NOT NULL,from_visit INTEGER,transition INTEGER DEFAULhT 0 NOT NULL,segment_id INTEGER,is_indexed BOOLEAN)

The table's result is :

1|10|12979306250150765|0|268435457|1|0

17|14|12979306291009421|0|838860801|2|0

18|14|12979306291724492|0|805306368|0|0

19|14|12979306296042195|0|838860808|0|0

20|14|12979306296322297|0|805306368|0|0

21|14|12979306298922534|0|838860808|0|0

22|14|12979306299261896|0|805306368|0|0

23|15|12979306305614105|0|805306368|0|0

24|15|12979306310110294|0|805306368|0|1

25|16|12979306316672464|0|805306368|0|1

So, another table called urls, which is also in this history sqlite is:

CREATE TABLE "urls"(id INTEGER PRIMARY KEY,url LONGVARCHAR,title LONGVARCHAR,visit_count INTEGER DEFAULT 0 NOT NULL,typed_count INTEGER DEFAULT 0 NOT NULL,last_visit_time INTEGER NOT NULL,hidden INTEGER DEFAULT 0 NOT NULL,favicon_id INTEGER DEFAULT 0 NOT NULL)

The table's result is:

1|http://cateee.net/xxxx|Linuxxxx|0|0|0|0|0
2|http://kernel.org/|Index of xxxxxxxxx|0|0|0|0|0
3|http://repo.orxxxxxxxxxxxxx|xxx|0|0|0|0|0
4|http://stackoverflow.com/xxxx|xxxxxx|7|0|12979644275181782|0|0
5|http://stackoverflow.com/questions/xxxxxxx|linuxxxxxxxxxxxxxxxx|0|0|0|0|0
6|http://www.db-class.org/xxxxxxxxxxxxxxxx|xxxxxxx|6|0|12979306496245203|0|0
7|http://www.xxxxxxxxxxxxxxxxxxx|xxxxxxxxxxxxxxxxxx|0|0|0|0|0
8|http://www.xxxxxxxxxxxxxxxx|xxxxxxxxxxxxxxxxxxx|0|0|0|0|0
10|http://www.google.com/|PYTHON - Googlexxxxxxxxx|1|1|12979306250150765|0|0
14|http://www.facebook.com/|Facebook|6|2|12979306750272709|0|0
     15|http://www.facebook.com/profile.phpxxxxxxxxx|xxxxxxxxxxxxxxx|2|0|12979306310110294|0|0

So my problem are: is the urls table's first column called url_id represents in the visits table's second column called url INTEGER, but the relationship is not clear. And what is the meaning of transition INTEGER in visits table, can I extract the time from these, I need to get useful information from these tables, and make their relationship clear.

Solution

This site had a lot of helpful information about Chrome's SQLite tables, and how to query the tables.

An example they give on that page of joining the two tables "urls" and "visits" is as follows:

SELECT urls.url, urls.title, urls.visit_count, urls.typed_count, urls.last_visit_time, urls.hidden, visits.visit_time, visits.from_visit, visits.transition
FROM urls, visits
WHERE
 urls.id = visits.url

And as for the "transition" field in the visits table, this value tells you how the URL was loaded into the browser. I'll let you check out the link I gave you for more details.

Comments

[Vikas Bansal]

there is a column in there in visits table named as visit_duration. Please confirm if its in milliseconds or microseconds.

[Jishan]

@VikasBansal Did you find if its in milliseconds or microseconds?