How to intepret the date in the database?

[Pauler221]

Checklist

• I made sure that there are no existing issues or discussions - open or closed - which I could contribute my information to.
• I have read the FAQ and my question isn't listed.
• I have taken the time to fill in all the required details. I understand that the question will be dismissed otherwise.
• I have read and understood the contribution guidelines.

What is/are your question(s)?

Hello, I'm trying to find a video I once watched. Since NewPipe doesn't allow you to search through your watch history I opened up the database to do it manually.

Now after inspecting "streams", "stream_history" and "search_history" closely I cannot intepret the "crearion_date" format. It's a 13 digit number that doesn't seem to represent a real date format.

I also want to sort the "search_history" and "streams" table by date.

Additional information

No response

[aaferrari]

Since NewPipe doesn't allow you to search through your watch history I opened up the database to do it manually.

I also usually do the same (and I agree that a way to search through already watched videos is missing).

It's a 13 digit number that doesn't seem to represent a real date format.

In fact it is a unixepoch with the milliseconds included. What you can do is to format this to something more readable with these queries:

select *, strftime('%Y-%m-%d %H:%M:%S', datetime(substr(upload_date, '1', 10)||'.'||substr(upload_date, 11), 'unixepoch', 'localtime')) as human_date from streams;
select *, strftime('%Y-%m-%d %H:%M:%S', datetime(substr(access_date, '1' ,10)||'.'||substr(access_date, 11), 'unixepoch', 'localtime')) as human_date from stream_history;
select *, strftime('%Y-%m-%d %H:%M:%S', datetime(substr(creation_date, '1' ,10)||'.'||substr(creation_date, 11), 'unixepoch', 'localtime')) as human_date from search_history;

What they do roughly is show an extra column where the Unix time is first converted to decimal, , then converted to a timestamp in ISO8601 format and finally formatted into a string (this last step is optional, but serves to show the timestamp in another format if you don't like the default returned by datetime()).

Although if you plan to use this often, it might be more practical to do it through views.

I also want to sort the "search_history" and "streams" table by date.

You can check this and sort by [upload|creation|access]_date directly.

[Pauler221]

Sorry for the late reply.

Thank you for taking your time to write a detailed explanation! It helped.