Does anyone know what I need to change in the PLEX SQLite library database in order to change how TV shows are listed when I select the “By Last Episode Added” sort option? I’ve tried changing different _at (datetime) fields in the metadata_items (for episodes and for seasons) as well as in the media_items and media_parts tables and no change. I also posted here and someone recommended I try this Development forum.
Anyone?
Trying to bump this once more. Anybody please?
I’ve done this a fair bit for movies but never tv media. It’s the metadata_items table and added_at that control this. Example below.
UPDATE metadata_items SET added_at = xxxx WHERE id = yyyy;
Hope this helps.
1 Like
Unfortunately, that’s one of the many fields I’ve changed within no effect. So far, I’ve changed all these below and the order of the shows doesn’t change. In every case, I first stopped the PLEX server, dropped the two triggers, modified the values of the fields, re-created the 2 triggers and started the server. No luck.
Seasons (metadata_items where metadata_type = 3):
- added_at
- updated_at
- refreshed_at
Episodes (metadata_items where medatata_type = 4):
- created_at
- added_at
- updated_at
- refreshed_at
Media Items (media_items):
- created_at
- updated_at
Media Parts, files (media_parts):
- created_at
- updated_at
Okay, so ran a quick test. I added a show today to my server which is right at the front of my Recently Added in TV Shows list.
I examined the db and can see the date/time added for the show is 1665133285 (Epoch/Unix Timestamp). I then ran the following to update the added_at making it the same value as originally_available_at, in essence putting it into the past for recently added.
UPDATE metadata_items SET added_at = originally_available_at WHERE added_at <> originally_available_at AND originally_available_at IS NOT NULL AND created_at >= 1665133285 AND library_section_id = 2;
I used created_at >= 1665133285 to make sure to only update that show/season/episodes but you might want to expand on that depending on your needs. You could do a created_at >= xxxx AND created_at <= yyyy
BTW… library_section_id is 2 for me as that is my TV library id. It might be something else for you.
You can use a simple select statement to see what would be updated beforehand, example below so you can be sure what is being updated. Anyhow, backup of db is your friend here (just in case).
SELECT id, title, added_at from metadata_items WHERE added_at <> originally_available_at AND originally_available_at IS NOT NULL AND created_at >= 1665133285 AND library_section_id = 2;
Doing this the show was moved from the front of my Recently Added in TV Shows list and moved to the date it was originally available. I altered no other tables.
Hope this is of some help.
1 Like
I’m bringing this subject up hoping to get further help, since this never worked for me. I am now trying to do the same thing to something simpler: movies.
I see that what @anon5074910 modified was the originally_available_at field in the metadata_items table, which basically is the date the production was released and would affect the order of items when selecting the “Release Date” sort. That’s not what I want to change. I want to change the order in which items show when I select “By Date Added”. I want the latest items I add to my library to always show first.
I have successfully modified both added_at and created_at fields in the metadata_items table and this makes no change in the sorting order. I even went one step further and also changed updated_at in both the media_items and media_parts tables for this item (created_at is blank in both, so I didn’t think to update this) and still nothing.
In fact, to make sure I am testing this right, I am using the same values of another item in my library, meaning the one I’m updating should appear next to it after these changes. Not at all.
I always stop my server before the update and start it after. I’ve even tried re-scanning library files and even refreshing the metadata of the item I’m updating and the order in which it shows never changes. By the way, this is PLEX on a Synology NAS. Not sure if that makes a difference. It shouldn’t, since it’s the same SQLite db.
Any other ideas?
By the way, I also noticed these triggers mentioned above in this thread no longer exist in the latest versions of PLEX. Not sure how relevant that is.
Thanks in advance and @anon5074910, I know it’s been a long time, but I have been meaning to thank you for taking the time back then.
Can anybody please try this? I’m out of ideas. 
Just saw this thread. What are you trying to do exactly? Changing the dates in DB may not affect what you get in Web. Please be specific about what you are expecting.
1 Like
I was specific, but I do realize it’s a lot to read in the rest of the thread. hehe Let me explain again here and in as few words as possible.
I want my movies to be sorted in the order I want when I select the “By Date Added” sort option. This is the sort I always use. For some unknown reason, my Plex library will sometimes mess up and a bunch of movies I added long ago will show up at the top of the list again, saying “Just added” when that’s not true. I want to find out what table(s) and field(s) in the SQLite db I need to update in order to send these back to the date in which the files were added to my file system. I have already tried modifying all of the fields mentioned in my previous post and the order doesn’t change at all. I always stop the Plex server before the update and start it after. Which element(s) in the db is Plex looking at to decide how to sort items when choosing “By Date Added”?
Think that’s enough information to assist? If not, please feel free to ask anything else you’d like to know.
Thanks again for trying to help.
Can you provide a screenshot of the exact page you are looking at? There are a few ways to sort this.
The “date added” is the “added_at” field in the metadata_items table. For example, I change all my added_at dates for movies and episodes to match the release date. So for me sorting by “Recently Added” or “Recently Released” produces the same results.
Keep in mind that for TV shows, this is at the episode level. We also have added_at dates for the season and the show, so if you are viewing your show library “by shows” and sort by “date added”, this uses the show’s added date, not the last episode. There is a “last episode dare added” filter.
There is a bug around this where if you have more than 1 filter, it ends up using the show date instead of the episode date. This may be what you are seeing if previous changes have not produced the result you are looking for. That’s why I asked for specifics to see if this is the case.
1 Like
Absolutely. Here you go. Keep in mind today is 4/13/2023, so 16 days ago is 3/28/2023.
Before the SQLite db change:
After the SQLite db change (stop server, make change, start server):
Wait… This worked! And I think I just realized what’s been happening to me all this time and it could be related to the client I am using to update the SQLite db. I am using DBeaver and I just noticed that while the connection is open, the .db file isn’t changed (based on the last update date/time). However, the .db-wal file is. As soon as I drop the connection, the .db file seems to get dropped and the .db-wal renamed as .db. Before, I would just copy the .db file from my computer (where I am updating it) to the Plex server location on my Synology NAS, simply overwriting it with the exact same file, without any changes. My movie is finally showing in the place I want it to show.
Had I not gone through this exercise of providing screenshots to show you the issue, I probably wouldn’t have noticed what was happening, so thank you. I also wanted to leave this here in case others are using DBeaver and not realizing this.
Thank you and thank @anon5074910 once again.
1 Like
By the way, I was able to update one movie hardcoding the metadata ID. However, I haven’t been able to do this to all the items in my library because apparently SQLite doesn’t support a statement like this one below, claiming there’s no table called “i”, which is true. This would, however, work in other relational databases like SQL Server. How can I achieve something like this here?
with
metadata_update as (
select
i.id
, m.updated_at media_updated_at
from
metadata_items i
left outer join media_items m on i.id = m.metadata_item_id
where
i.library_section_id = 8
and cast(m.updated_at as date) <> cast(i.added_at as date)
)
update i
set
created_at = u.media_updated_at
, added_at = u.media_updated_at
from
metadata_items as i
inner join metadata_update as u
on u.id = i.id
where
i.library_section_id = 8
For whoever is stuck in the same place, I found my answer in the SQLite official site, specifically the 2.2 UPDATE FROM section of this page.
update metadata_items
set
created_at = metadata_update.media_updated_at
, added_at = metadata_update.media_updated_at
from (
select
i.id metadata_id
, min(m.updated_at) media_updated_at
from
metadata_items i
left outer join media_items m on i.id = m.metadata_item_id
where
i.library_section_id = 8 -- My "Movies" library
and i.metadata_type = 1 -- movie (not collection)
and m.updated_at is not null
and (i.added_at is null
or i.created_at is null
or cast(m.updated_at as date) <> cast(i.added_at as date)
)
group by
i.id
) as metadata_update
where
id = metadata_update.metadata_id
Yeah, some DB editors require a “save” step before changes are made. I use DB Browser and it has a “Write changes” before any edits are actually applied to the file.
SQLite doesn’t support a statement like this one below
I’ve never seen a statement like that so 
.
1 Like
