Date added by file mtime or ctime in Plex

Plex Media Server Desktops & Laptops
1

Hi, is there a way, in Plex,  to make the library updates sort the items by ctime or mtime like in xbmc "advancedsettings.xml" options? 3.48

Options specific to the Video Library

  <videolibrary>
    <dateadded>2</dateadded> 
  </videolibrary>
2

Sorry I think I was suppose to post in regular forums...

Will post it there but I can't see any option to delete this one... so sorry in advance for double post...

3

Well, I never got an answer so I'm "asking a ninja" now...

I was wondering if there was any way to tell PMS (now on 9.8.8) to set the added dates ("recently added sort") for scanned items in Plex library based on their file date or even by "date added" entry in a NFO or other file 

And if not, should I do a request for it (or it's already planned)?

This can cause issues when adding an existing media folder in library. If users want to sort by date added, they get the date it was actually added to Plex (makes no sense when adding in batch)

***Update:

Sorry, did more research on forum and found this:

http://forums.plexapp.com/index.php/topic/44965-sort-by-file-date-instead-of-when-added-to-plex/

Not officially a request though....

 
Except if there is a way I haven't seen/found, you can ignore this tread I guess...
Really hope you guy consider it as I feel It's an important feature.
4

From a very old and long post for plex database manipulation (pre windows era for plex) but works great every time for me. Restart PMS after running the following 3 commands in terminal assuming that you are running PMS on a Mac. You can copy-paste each command for here to terminal. This will sort them by added date (not modified). Let me know if it works for you.


cd ~/Library/Application\ Support/Plex\ Media\ Server/Plug-in\ Support/Databases


sqlite3 com.plexapp.plugins.library.db


update metadata_items set added_at=(select media_items.updated_at from media_items where media_items.metadata_item_id=metadata_items.id);

5

Found the original post dated back in Oct. 2011 from SolarPlex so all credits go to him.

http://forums.plexapp.com/index.php/topic/31636-howto-query-data-from-plex-on-a-mac/


It’s quite long but really useful. Long story sort though run the 3 commands on the previous post and you are good to go.

6

What if the PMS is on Windows (Windows 2008 server R2)?

7

Ok, I think I managed...

-I downloaded a copy of SQLITE3.exe (http://www.sqlite.org/download.html)

-I placed it directly in "C:\Users\Administrator\AppData\Local\Plex Media Server\Plug-in Support\Databases"

-I ran in a command prompt the two last commands:

2013-10-19%203-06-20%20AM.jpg

-Restarted PMS

It looked like it worked...

To be really sure, I deleted one of my collections and re added it (let Plex scan it, etc...)

At this point, it wasn't sort by file date but by "Plex added to library sort"

I did the smal procedure again; WORKED!!!

I presume this need to be run every time to "resort" if needed, right?

This was too simple, THANK YOU.

***I really can't understand why this hasn't been added in PMS settings...  It would be sooo easy to have a setting users could enable and this simply SQL query would run after scans or something similar...

To my knowledge, Plex in based on XBMC (I understand that a lot has happen since but I think it would be doable to make this an official option for Plex users (like XBMC in my first post of this tread).

Hope this will be considered....

Again, Losangeles, thank you! (nobody answered me for so long on this tread, I appreciate it)

8

To my knowledge, Plex in based on XBMC

PMS has nothing to do with XMBC.

PMC (and it's replacement, Plex/HT) are based on XMBC, but nothing else in the Plex world is.

9

Glad it worked for you!

The only time I needed to run this was when I did a couple -or more- of fresh installs of plex during the 3+ years I’ve being using it. That is when everything is deleted (database and metadata) and cataloging starts from scratch. From that point on plex does sort new items according to added date.

10

PMS has nothing to do with XMBC.
 
PMC (and it's replacement, Plex/HT) are based on XMBC, but nothing else in the Plex world is.


Ah ok, I always thought PMS library system was based (originally) on XBMC.
Thanks
11

I'm not exactly sure if you are trying to achieve the same Sort Order as I am, but I have a topic here which I've detailed my current solution.  Still waiting on one last piece to make it even better, but it already works.  This would only affect TV Show records in the database (shows that have seasons that have episodes, ie. items that have items that have items):

http://forums.plexapp.com/index.php/topic/83543-run-a-post-scan-hook-to-update-date-added/

12

Moving this to regular forums so non-plexpass folks can benefit also

13

This post was super helpful to me, so I thought I should share what I learned and came up with.
 
If you're on Linux, you can use "touch -t" to change the file modification times on your media files, then use sqlite3 and update the metadata to change the date added to the file date, sort of like the script below.
 
Of course a new version of the server with a different schema completely wrecks this, but hopefully they'll just have a feature like this built in.
 
Update; I found that for Music libraries, the Artist and Album "added_at" fields don't seem to get populated when added to the library, so "Recently Added" isn't very reliable.
 
I updated the script to modify the metadata for Music artists and albums from their oldest child item, after updating the created_at and updated_at times for the media part and media item records to match the actual media file modification time, and then updating the metadata part records added_at, created_at, and updated_at times to match the media item records.
 
I've included the script and it's SQL files below;

Script;

#!/bin/bash

export sqldir="${HOME}/sql"

export dbdir=’/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases’

export dbname="${dbdir}/com.plexapp.plugins.library.db"


if test ! -f “${dbname}”

then

echo “Database file does not exist: ${dbname}.” 1>&2

exit 1

fi


if test ! -r “${dbname}”

then

echo “Cannot open for reading: ${dbname}” 1>&2

exit 1

fi


if test ! -w “${dbname}”

then

echo “Cannot open for writing: ${dbname}” 1>&2

exit 1

fi


declare -a sql=(selectMyMedia.sql updateMediaItemTimes.sql updateMetadataItemTimes.sql updateAlbumTimes.sql updateArtistTimes.sql)


for item in ${sql[*]}

do

if test ! -f “${sqldir}/${item}”

then

echo “SQL command file ${item} is not in ${sqldir}” 1>&2

exit 1

fi

done


if test -n “pgrep -f 'plexmediaserver'”

then

echo “Plex Media Server should be stopped before running this procedure.” 1>&2

exit 1

fi


export bfs=${IFS}

export IFS=’|’


cd “${dbdir}”


declare -a files

declare -a ctimes

declare -a utimes

declare -a idlist


let i=0

let j=0

let x=0


echo “Reading Media Library File MetaData…”


while read id ctime utime filename

do

if test ! -f “${filename}”

then

echo “Media file ‘${filename}’ does not exist.” 1>&2

continue

fi


files[x]=${filename}
ctimes[x]="${ctime}"
utimes[x]="${utime}"
idlist[x]="${id}"

let x=x+1



done < <( sqlite3 -noheader -list “${dbname}” < “${sqldir}/${sql[0]}” )


echo “Media Files Read  : $x”

echo “Media Files Stored: ${#files[*]}”

echo

echo “Comparing Library Times to File Times…”


if test ${x} -lt 1 -o ${x} -ne ${#files[*]}

then

echo “Error in file collation…” 1>&2

exit 1

fi


export IFS=${bfs}


while read mdate mtime utcx filename

do

if test “${files*}” != “${filename}”

then

echo

echo “Error…”

echo “Filename read  : ‘${filename}’”

echo “Filename sought: ‘${files*]}’”

echo “ID sought      : ‘${idlist*]}’”

echo “Sequence       : ${i}”

exit

fi


export ftime="${mdate} ${mtime:0:8}"

if test "${ctimes*]}" != "${ftime}" -o "${utimes*]}" != "${ftime}"
then
	export id="${idlist*]}"

	echo
	echo "Filename       : '${filename}'"
	echo "Media ID       : '${id}'"
	echo "File Time      : '${ftime}'"
	echo "Library CTime  : '${ctimes*]}'"
	echo "Library UTime  : '${utimes*]}'"
	echo "Sequence       : ${i}"
	echo

	sqlite3 -echo "${dbname}"\
		"UPDATE media_parts SET created_at='${ftime}', updated_at='${ftime}' WHERE id=${id};"
fi

let i=i+1



done < <(

while test ${j} -lt ${x}

do

echo “${files[j]}”

let j=j+1

done |

tr ’

’ ‘\0’ |

xargs -0 stat --printf '%y %n

’

)


for i in 1 2 3 4

do

echo

echo “Running SQL Script ‘${sqldir}/${sql*}’…”

sqlite3 “${dbname}” <  “${sqldir}/${sql*}”

done


echo

selectMyMedia.sql;

SELECT
	id,
	created_at,
	updated_at,
	file
FROM media_parts
WHERE IFNULL( file, '') <> '';

updateMediaItemTimes.sql;

UPDATE media_items
	SET created_at = (
		SELECT MIN( media_parts.created_at) FROM media_parts
			WHERE media_parts.media_item_id = media_items.id
			AND IFNULL( media_parts.created_at, '') <> ''),
	updated_at =  (
		SELECT MIN( media_parts.created_at) FROM media_parts
			WHERE media_parts.media_item_id = media_items.id
			AND IFNULL( media_parts.created_at, '') <> '')
WHERE EXISTS(
		SELECT media_parts.created_at FROM media_parts
			WHERE media_parts.media_item_id=media_items.id
			AND IFNULL( media_parts.created_at, '') <> '');

updateMetadataItemTimes.sql;

UPDATE metadata_items
	SET added_at = (
		SELECT MIN( media_items.created_at) FROM media_items
			WHERE media_items.metadata_item_id = metadata_items.id
				AND IFNULL( media_items.created_at, '') <> ''),
	created_at =  (
		SELECT MIN( media_items.created_at) FROM media_items
			WHERE media_items.metadata_item_id = metadata_items.id
				AND IFNULL( media_items.created_at, '') <> ''),
	updated_at = (
		SELECT MIN( media_items.created_at) FROM media_items
			WHERE media_items.metadata_item_id = metadata_items.id
				AND IFNULL( media_items.created_at, '') <> '')
WHERE EXISTS(
	SELECT media_items.created_at FROM media_items
		WHERE media_items.metadata_item_id = metadata_items.id
			AND IFNULL( media_items.created_at, '') <> '');

updateAlbumTimes.sql;

UPDATE metadata_items
	SET added_at = (
		SELECT MIN(mdi2.added_at) FROM metadata_items as mdi2
			WHERE mdi2.parent_id = metadata_items.id
			AND IFNULL( mdi2.added_at, '') <> '' ),
	created_at = (
		SELECT MIN(mdi2.added_at) FROM metadata_items as mdi2
			WHERE mdi2.parent_id = metadata_items.id
			AND IFNULL( mdi2.added_at, '') <> '' ),
	updated_at = (
		SELECT MIN(mdi2.added_at) FROM metadata_items as mdi2
			WHERE mdi2.parent_id = metadata_items.id
			AND IFNULL( mdi2.added_at, '') <> '' )
WHERE metadata_type = '9';

updateArtistTimes.sql;

UPDATE metadata_items
	SET added_at = (
		SELECT MIN(mdi2.added_at) FROM metadata_items as mdi2
			WHERE mdi2.parent_id = metadata_items.id
			AND IFNULL( mdi2.added_at, '') <> '' ),
	created_at = (
		SELECT MIN(mdi2.added_at) FROM metadata_items as mdi2
			WHERE mdi2.parent_id = metadata_items.id
			AND IFNULL( mdi2.added_at, '') <> '' ),
	updated_at = (
		SELECT MIN(mdi2.added_at) FROM metadata_items as mdi2
			WHERE mdi2.parent_id = metadata_items.id
			AND IFNULL( mdi2.added_at, '') <> '' )
WHERE metadata_type = '8';
1 Like
14

Somehow I duped the previous post, can't see a way to delete this.

15

Does ANYONE know how I would do the same thing if PLEX is installed on my new Synology DS018+ NAS??? Help!

16

Yeah the above will mostly still work, but you will want to play around with the script on a copied version of your database to simulate the changes first.

Also wanted to give a big shout-out to markat2k for his help and contributions above. It has definitely saved me so much time digging through and trying to understand how artists and albums are stored. The script and queries might need a little adjustment now, 5 years later but it won’t be much.

17

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.