SQL query to select and show human readable date

(1) By chriskuku on 2025-02-08 16:34:57 [source]

I have the following SQL query in sqlite3. I queries the mozilla (Firefox) places.sqlite in the profiles directoy and the aim is to select all urls that fall into the year 2021 and have a certain pattern in the .

Problem is that the date is an INTEGER (unix time in microseconds ?) and I would like to have it shown as something like 2021-06-15.

sqlite3 ./places.sqlite "SELECT url, last_visit_date FROM moz_places WHERE url LIKE '%www.somesite.net%' AND last_visit_date BETWEEN strftime('%s','2021-01-01')*1000000 AND strftime('%s','2021-12-31')*1000000 "

The result looks like this:

https://www.somesite.net/topic/64283	1631522732435569

How can I make the second field appear as a neatly formatted human readable date string?

(2) By John Dennis (jdennis) on 2025-02-09 01:28:45 in reply to 1 [link] [source]

sqlite> select datetime(1631522732.435569,'unixepoch');
2021-09-13 08:45:32

(3) By anonymous on 2025-02-09 04:11:30 in reply to 1 [link] [source]

This may be a good place to look also https://www.sqlite.org/lang_datefunc.html.

(4) By chriskuku on 2025-02-09 08:00:57 in reply to 1 [link] [source]

Thanks for answering and leading me into the right direction for further reading.

My actual problem - due to the lack of understanding SQL - was how to formulate the SELECT statement regarding the column "last_visit_date". I came out with the following:

"SELECT url, strftime('%m-%d-%Y', last_visit_date, 'unixepoch') AS last_visit_date FROM moz_places WHERE url LIKE '%www.somesite.net%' AND last_visit_date BETWEEN strftime('%s','2021-01-01')*1000000 AND strftime('%s','2021-12-31')*1000000 "

(5) By Adrian Ho (lexfiend) on 2025-02-09 09:10:36 in reply to 4 [link] [source]

There are still at least two problems with your query:

Your input last_visit_date is in microseconds, so strftime() goes out-of-range and that entire result column is NULL.
The upper bound of your BETWEEN expression stops short at midnight of Dec 31, so any URLs visited on Dec 31 itself aren't matched.

Try this instead:

SELECT url, strftime('%m-%d-%Y', last_visit_date/1000000, 'unixepoch') AS last_visit_date
  FROM moz_places
 WHERE url LIKE '%www.somesite.net%'
   AND last_visit_date BETWEEN strftime('%s','2021-01-01')*1000000
                           AND strftime('%s','2022-01-01')*1000000-1

(6) By Spindrift (spindrift) on 2025-02-09 09:25:22 in reply to 5 [link] [source]

And note also that you can substitute 1e6 for all those factors of a million if you so desire.

I'm actually uncertain if this introduces a potential float precision vulnerability in the code, though it is unlikely to be a problem in your specific case (be fascinating to know if edge cases might cause an incorrect date to be selected very close to midnight for example).