Sorting library by file date?

[Phssthpok]

Is it possible to access the file date (or latest file date if there are multiple formats) for use as a column that I can sort on? I have "Modified" as a column, but if I delete one format from a book with multiple formats, the book shows up as having been modified. I want to keep track of which books I've added or edited recently; I don't want books were I delete unwanted formats to jump to the top of the list.

[chaley]

You can do what you want but it might be very slow, especially for larger libraries. Calibre will need frequently to check the format file mod times. If you sort by the column then calibre must retrieve the mod time of every format file. With that proviso ...

Create a custom column based on other columns. Don't use the "acts like tags" variant. Set it to sort by date. Put the following into the template field:

program:
	sublist(list_re(formats_modtimes('yyyy-MM-dd hh:mm:ss'), ',', '.*?:(.*)', '\1'), 0, 1, ',')

[Phssthpok]

Best way would be to have a timestamp in the DB that is only updated when a book is added, edited & saved or metadata updated... but Calibre doesn't seem to keep track of those events separately from deletions.

[Phssthpok]

Originally Posted by Phssthpok

Best way would be to have a timestamp in the DB that is only updated when a book is added, edited & saved or metadata updated... but Calibre doesn't seem to keep track of those events separately from deletions.

Actually, all it would take would be for the Modified timestamp NOT to be updated when deleting a subset of the formats for a book... Kovid? Pretty please?

[eschwartz]

But that would be a total lie.

Last modified refers to the book record.

Deleting a book is a type of modification of the associated book record. In other words, that column was specifically designed to update when you delete a book.

calibre will NOT change in an arbitrary manner simply to suit one person's arbitrary use case. I will bet money on it.

[theducks]

I agree with eschwartz on both

I have a #edited timestamp column I manually maintain.
It would be possible to modify a user plugin that modifies a BOOK FILE, touch that field . Grab you Python Book and give it a whack

[chaley]

Originally Posted by Phssthpok

Is it possible to access the file date (or latest file date if there are multiple formats) for use as a column that I can sort on?

And you are welcome. Glad to have spent a half-hour working out a solution for what you asked for.

[Phssthpok]

Originally Posted by chaley

And you are welcome. Glad to have spent a half-hour working out a solution for what you asked for.

Apols. Thank you for your input. As you say, it'll be slow though, so I'm still thinking about it...

[BetterRed]

@phssthpok - for one library I do similar to theducks, I have a custom column called Last Action Date. There's also an associated custom column with a fixed set of values called Last Action.

I also wrote a utility, for each book it determines latest file modified timestamp and filetype, which it writes to a CSV. I then use the Import List PI to read the CSV and update a couple of custom columns - Latest File Type, Latest File Date. Of course, the columns are almost never up-to-date (normally I don't even display them), I don't often need them, so when I do I run the script and Import List. I found using a template made calibre very sluggish - but that may have been in version 1.x.

My utility is written in C (because that's what I know best), but the same thing could be done in a script in two shakes of a lambs tail - but I loathe scripting languages.

BR

[kovidgoyal]

Not going to happen. The set of formats of a book are part of the information a book record stores. Changing that set of formats is changing the book record.

[chaley]

Originally Posted by BetterRed

My utility is written in C (because that's what I know best), but the same thing could be done in a script in two shakes of a lambs tail - but I loathe scripting languages.

You could do it in python as a stand-alone program using the calibre API, writing the columns directly and avoiding the import.

Here is a skeleton program that does the job.

def init_cache(library_path):
	from calibre.db.backend import DB
	from calibre.db.cache import Cache
	backend = DB(library_path)
	cache = Cache(backend)
	cache.init()
	return cache

# UNDEFINED_DATE is the smallest date that can appear in a calibre library
from calibre.utils.date import UNDEFINED_DATE
	
cache = init_cache(library_path = sys.argv[1])

formats_dict = {}
formats_modtimes_dict = {}

# Loop through all the books in the library
for id_ in cache.all_book_ids():
	latest_format = None
	latest_modtime = UNDEFINED_DATE
	
	# Loop through all the formats for the book, finding the newest one.
	formats = cache.field_for('formats', id_)
	for format in formats:
		format_data = cache.format_metadata(id_, format)
		if format_data['mtime'] > latest_modtime:
			# Format is newer
			latest_modtime = format_data['mtime']
			latest_format = format
	# If we found a format, save it in the result dict so we can 
	# write the column later
	if latest_format is not None:
		formats_dict[id_] = latest_format
		formats_modtimes_dict[id_] = latest_modtime
		
# Print the results for test.
#for id_ in formats_dict:
#	print formats_dict[id_], formats_modtimes_dict[id_]

# Write the results to the db
cache.set_field('#your_text_col_name', formats_dict)
cache.set_field('#your_date_col_name', formats_modtimes_dict)

If you name it modtimes.py then you would run it as

calibre-debug -e modtimes.py /path/to/library

[Phssthpok]

Great. Many thanks all, I'll try and give each of these suggestions a whirl over the weekend.

Basically the reason behind my request is this: I keep books as EPUBs. I edit them, give them a 1 star rating when they're OK, then upload to my Kindle to read and do final proofing. They're converted to AZW3 for the upload. Afterwards I delete the AZW3 so that if they need further editing I will edit the EPUB and reconvert before uploading, to avoid accidentally uploading the old AZW3. However I then need to see which books have been changed "recently", but it turns out that they all have since I deleted the AZW3... so I end up resorting to pencil & paper to keep track...

[LadyKate]

Add a column you manually change. I would assume you don't change that many books in a given session that it would be a major pain to manually note you worked on a book.

[Phssthpok]

Originally Posted by chaley

You can do what you want but it might be very slow, especially for larger libraries. Calibre will need frequently to check the format file mod times. If you sort by the column then calibre must retrieve the mod time of every format file. With that proviso ...

After being horribly busy with other things for the last few weeks I finally got around to trying this, with a slight mod (since I only edit EPUBs):

program: select(formats_modtimes('yyyy-MM-dd hh:mm:ss'),'EPUB')

This works fine, and seems to sort pretty much instantly on a library of 5000+ books. I don't know when Calibre checks the mod times, but the column updates instantly when I save a book from the editor.

The only issue I have now is that the result of all this is a string. Is there any way to turn it back into a datetime and format it nicely for display while sorting it only the underlying datetime value? datetime.strptime() would presumably do the trick if only I could import datetime somehow...

But this would just be the icing on the cake... as it is, your solution does exactly what I wanted. Thank you very much.

[eschwartz]

You can define your own custom python function for templates.