For how I often use SeaMonkey's browser, it would be convenient to have
an _auxiliary_ history tool that could:
1. track first and last time a URL was accessed.
2. how many times that URL had been accessed.
3. search features:
a. find specified date or date range.
b. Boolean combination of search criteria
[ability to ignore specific search engines very desirable]

I most definitely *DO NOT* recommend these be added to default browser.

On 25/11/2023 15:30, Richard Owlett wrote:
> For how I often use SeaMonkey's browser, it would be convenient to have
> an _auxiliary_ history tool that could:
> 1. track first and last time a URL was accessed.
> 2. how many times that URL had been accessed.
> 3. search features:
>    a. find specified date or date range.
>    b. Boolean combination of search criteria
>       [ability to ignore specific search engines very desirable]
>
> I most definitely *DO NOT* recommend these be added to default browser.
>

In the local profile directory, places.sqlite is the database file
containing this information. Using SQLite3 command-line tools or various
GUI tools depending on the platform you can formulate and run SQL
queries against the DB:

1. When visited

The places DB has a table of places (URLs for our purpose) and visits
(occasions when a place was visited). A lot of information is available
in the places (moz_places, aliased as p) table but here we have to join
it (p.id = v.place_id) with the visits (moz_historyvisits, aliased as v)
table. The visit times are stored as microseconds since 1970-01-01
00:00.000000 (the epoch), so they get divided by 1000000 to get seconds
since then. The SQLite function datetime() makes a readable date out of
such a time value when the second parameter is 'unixepoch'.

select datetime(v.visit_date/1000000, 'unixepoch') as when_visited from
moz_places p, moz_historyvisits v where p.id = v.place_id and p.url =
'https://www.seamonkey-project.org/start/' order by v.visit_date asc;

when_visited
2021-09-01 10:20:49
2021-09-06 14:15:11
2021-10-02 09:43:08
2021-10-02 09:47:12
2023-08-05 17:59:45
2023-09-21 15:48:28
2023-10-02 05:45:10
2023-10-04 11:17:03
2023-10-09 12:08:04
2023-10-10 22:22:20

2. Visit count

select p.visit_count from moz_places p where p.url =
'https://www.seamonkey-project.org/start/';

visit_count
9

Good, matches (1).

3. Search within range

Excluding Google, URLs visited over 20-21 October. This uses a nested
query. There are many other ways to formulate this.

select * from (select p.url, datetime(v.visit_date/1000000, 'unixepoch')
as when_visited from moz_places p, moz_historyvisits v where p.id =
v.place_id and not(p.url like 'https://%.google.%') order by
v.visit_date asc) where when_visited >= '2023-10-20' and when_visited <=
'2023-10-21';

url when_visited
http://imgur.com/gallery/Q95k 2023-10-20 00:46:44
https://imgur.com/gallery/Q95k 2023-10-20 00:46:45
....
https://api.imgur.com/post/v1/media/A61SaA1?client_id=546c25a59c58ad7&include=media,account
2023-10-20 12:12:43
https://api.imgur.com/post/v1/media/A61SaA1?client_id=546c25a59c58ad7&include=media,account
2023-10-20 12:12:49

So that's easy, then!

/df

--
London
UK

On 11/26/2023 01:59 PM, Dirk Fieldhouse wrote:
> On 25/11/2023 15:30, Richard Owlett wrote:
>> For how I often use SeaMonkey's browser, it would be convenient to
>> have an _auxiliary_ history tool that could:
>> 1. track first and last time a URL was accessed.
>> 2. how many times that URL had been accessed.
>> 3. search features:
>>     a. find specified date or date range.
>>     b. Boolean combination of search criteria
>>        [ability to ignore specific search engines very desirable]
>>
>> I most definitely *DO NOT* recommend these be added to default browser.
>>
>
> In the local profile directory, places.sqlite is the database file
> containing this information. Using SQLite3 command-line tools or various
> GUI tools depending on the platform you can formulate and run SQL
> queries against the DB:
>
> 1. When visited
>
> The places DB has a table of places (URLs for our purpose) and visits
> (occasions when a place was visited). A lot of information is available
> in the places (moz_places, aliased as p) table but here we have to join
> it (p.id = v.place_id) with the visits (moz_historyvisits, aliased as v)
> table. The visit times are stored as microseconds since 1970-01-01
> 00:00.000000 (the epoch), so they get divided by 1000000 to get seconds
> since then. The SQLite function datetime() makes a readable date out of
> such a time value when the second parameter is 'unixepoch'.
>
> select datetime(v.visit_date/1000000, 'unixepoch') as when_visited from
> moz_places p, moz_historyvisits v where p.id = v.place_id and p.url =
> 'https://www.seamonkey-project.org/start/' order by v.visit_date asc;
>
> when_visited
> 2021-09-01 10:20:49
> 2021-09-06 14:15:11
> 2021-10-02 09:43:08
> 2021-10-02 09:47:12
> 2023-08-05 17:59:45
> 2023-09-21 15:48:28
> 2023-10-02 05:45:10
> 2023-10-04 11:17:03
> 2023-10-09 12:08:04
> 2023-10-10 22:22:20
>
>
> 2. Visit count
>
> select p.visit_count from moz_places p where p.url =
> 'https://www.seamonkey-project.org/start/';
>
> visit_count
> 9
>
> Good, matches (1).
>
> 3. Search within range
>
> Excluding Google, URLs visited over 20-21 October. This uses a nested
> query. There are many other ways to formulate this.
>
> select * from (select p.url, datetime(v.visit_date/1000000, 'unixepoch')
> as when_visited from moz_places p, moz_historyvisits v where p.id =
> v.place_id and not(p.url like 'https://%.google.%') order by
> v.visit_date asc) where when_visited >= '2023-10-20' and when_visited <=
> '2023-10-21';
>
> url    when_visited
> http://imgur.com/gallery/Q95k    2023-10-20 00:46:44
> https://imgur.com/gallery/Q95k    2023-10-20 00:46:45
> ....
> https://api.imgur.com/post/v1/media/A61SaA1?client_id=546c25a59c58ad7&include=media,account
> 2023-10-20 12:12:43
> https://api.imgur.com/post/v1/media/A61SaA1?client_id=546c25a59c58ad7&include=media,account
> 2023-10-20 12:12:49
>
> So that's easy, then!
>
> /df
>

Easy? Sure if you have post dBaseII experience <GRIN>.
I made a stab at sqlite a few years ago.
Got nowhere. Over-reached? This may be a suitable problem to work on.
Any suggested sqlite tutorials? [this machine runs 32 bit Debian 9.13]
Thank you.

On 28/11/2023 13:03, Richard Owlett wrote:
>...> >
> Easy? Sure if you have post dBaseII experience <GRIN>.
> I made a stab at sqlite a few years ago.
> Got nowhere. Over-reached? This may be a suitable problem to work on.
> Any suggested sqlite tutorials? [this machine runs 32 bit Debian 9.13]

Unfortunately I came to SQLite with SQL experience so I just read the
product manuals, very good, online. I expect Debian 9 will be fine:
you'll need the sqlite3 package, which gives you the basic program with
its command-line query tool and (recommended) sqlitebrowser for the DB
Browser for SQLite GUI tool, maybe more familiar if someone has used
Access or dBase.

The top Ghit for "SQLite tutorial" was https://www.sqlitetutorial.net/
which seems OK from a quick look.

/df

--
London
UK

Dirk Fieldhouse wrote:

> The top Ghit for "SQLite tutorial" was https://www.sqlitetutorial.net/
> which seems OK from a quick look.

This was also covered in the thread

"Re: WANTED - Specialized browser history viewer - Linux OS"

03/06/2023, 19:33

A good place to start is 'man sqlite3', especially the commands that
begin with a dot