0

I have written code to fetch browser's history of websites visited by a user on his machine using Winforms C#.Net

However, I also need to fetch time he spent on each website, for example, output should be:

John, www.stackoverflow.com , 20 minutes.

Read History file using SQLIte connenction and populate datatable. Used approach from this link - https://social.msdn.microsoft.com/Forums/vstudio/en-US/3e9f3588-ad0b-49af-b269-2abfda0b9abc/how-to-get-the-browser-history-in-program-using-c?forum=csharpgeneral

But 'Visit_duration' column of table 'Visits' does not provide time spent on each website. Refer red circle in attached screenshot.

Please advise if I am using the correct column. If yes, how would I convert this column to get exact time in hours or minutes

enter image description here

enter image description here

Edits:

While @imsmn 's reply is good, but I also need a Sum of "Visit_duration" . Note that "URL" column of Table 2(Visits) matches "ID" column of Table 1(urls). So, I want Totalsum of Table 2 "Visit_duration" grouped on Table 2 "URL". The matching 'Where clause is, table1["ID") = table2["URL") . Kindly help

TkTech
  • 37
  • 7
  • 4
    Good luck - my browser has currently about 20 tabs open, some of them opened hours ago, looked at for a minute and never revisited - they'll get garbage collected when I close the browser on workday end ... – Patrick Artner Jun 16 '21 at 10:41
  • Could be an [XY problem](https://xyproblem.info/). – Uwe Keim Jun 16 '21 at 10:46
  • 1
    `last_visit_time` is [actually described in microseconds since 1601-01-01](https://stackoverflow.com/questions/20458406/what-is-the-format-of-chromes-timestamps). You can calculate the time based on that. That also applies to ```Visit_duration```. But as @PatrickArtner pointed out: That's probably not how long the user _actually stayed_ on the website. – devsmn Jun 16 '21 at 10:54
  • @imsmn , but I need time spent in hours or minutes. – TkTech Jun 16 '21 at 11:06
  • Time spent on a website != time a particular page was open in the browser != time spent not working (which is what I presume the ultimate goal here is) – Flater Jun 16 '21 at 11:27
  • @Flater , is there any way to get 'actual time' browsing ? – TkTech Jun 23 '21 at 05:42
  • @TkTech: To build an algorithm, you must first define what it does. Define 'actual browsing'. Are you going to track where the user's eyes are at? What they are thinking about? – Flater Jun 23 '21 at 15:45
  • @Flater , Customer wants to develop an application similar to https://desktime.com/ . Can you give some pointers now? – TkTech Jun 28 '21 at 06:03
  • @TkTech: Why are you asking for answers to a question where **you've already accepted an answer**? – Flater Jun 28 '21 at 10:16
  • @Flater , my account didn't allow post new questions for next few days, hence. – TkTech Jun 28 '21 at 13:15

1 Answers1

2

last_visit_time and visit_time (actually all timestamps) are stored in microseconds since 1601-01-01. Seems like that's default format for WebKit timestamps (chrome previously used webkit).

That means, all you have to do is adding the given microseconds to the base date.

DateTime baseDate = new DateTime(1601, 01, 01);
DateTime lastVisitDate = baseDate.AddSeconds(13268117172386444 / 1000000); // Convert to seconds by dividing with 1.000.000

With that example the result would be 14.06.2021 04:06:12.

Calculating the duration is just as easy - add the microseconds and subtract the base date.

TimeSpan duration = baseDate.AddSeconds(811945003 / 1000000).Subtract(baseDate);

In this case, the user spent 00:13:31 on the website.

devsmn
  • 1,031
  • 12
  • 21
  • Thanks @imsmn . This looks good. But I need a Sum of "Visit_duration" . Kindly see my comment as I cannot write code in this edit, – TkTech Jun 17 '21 at 11:57
  • @TkTech In that case a simple loop with a `Dictionary` should to the trick. All you'll have to do is to loop over all entries and fill the dictionary with the ID's as the key. If an ID already exists, add the `visit_time` to the value. – devsmn Jun 17 '21 at 14:26
  • Actually, these columns are really not giving a correct solution for Edge and firefox browsers . I shall need to post separately for this. Thank you for your solution. – TkTech Jun 17 '21 at 15:08