Fix "added" date based on file modified date
When I moved my files to a new server after a data loss event, all the new files appeared as "added" on that date even though they previously had been showing as added on the date I had originally added them in the previous server. I also didn't feel like recovering the old database and trying to mess with that.
In order to fix this, you will need to modify your database which requires shutting down the server and also using the Plex binary with a --sqlite flag in order to use their extensions. Some additional information on this part is here.
YMMV will vary on exact paths, etc., but for my Synology Plex, I used the following commands to get a sqlite3 prompt:
#!/bin/bash
SQLITE="/var/packages/Plex Media Server/target/Plex Media Server"
DB=./com.plexapp.plugins.library.db
cd "/volume1/Plex/Library/Application Support/Plex Media Server/Plug-in Support/Databases"
"$SQLITE" --sqlite $DB
Now the interesting part, and what I couldn't find on other sites, was how to get the file modification time. Luckily, it's not very far away. It's in media_items.updated_at and this table also has a metadata_item_id which maps to the table we need to update. So we can create a temp table that creates that mapping:
CREATE TEMP TABLE temp AS
SELECT R.updated_at AS new_added_at, S.id
FROM media_items R
INNER JOIN metadata_items S
ON R.metadata_item_id = S.id
WHERE S.library_section_id = 1
;
Note that for me library_section_id of 1 corresponds to my movies. Then we can update the added_at field:
UPDATE metadata_items
SET
added_at = (
SELECT new_added_at
FROM temp
WHERE temp.id=metadata_items.id
)
;
Restart your Plex server and now you should have all of your movies with their "Date Added" to correspond to the modification time of their file.
Posted Aug 21, 2021, 2:46 AM UTC
ive been working on this solution for days. i finally made an app to update all the date fields in the photos to be the same for each folder , then name that folder that date yyyy-mm-dd. hoping that would resolve this issue, but sadly not. im interested in doing this tweak, but im not familiar with sql anything or linux. i was hoping to write a batch file to do this. i found that plex comes with a mini sql, it it shows how to run 1 cmd at a time threw it. but i think you are logged into sql schell, maybe. can you help please!