More > JRiver Media Center 27 for Windows

Issue with FieldQuery() and dates

(1/3) > >>

markf2748:
When asked to return dates, FieldQuery() sometimes returns very large integers (billions) instead of either a date string or the usual floating point internal date format.  Here are some examples tested in Zelda (running directly in MC gives same results):

Expressions/returns which work ok:

[Name]
_25 Trips (back cover).jpg

[album]
25 Trips

[Media Type]
Image

[Type:Folder]
Image:D:\Music - Mark\Sierra Hull\25 Trips\

fieldquery(Type:Folder, Image:[Filename (path)], Name, 1, 0)
_25 Trips (digipack 02).jpg;_25 Trips (back cover).jpg;_25 Trips (digipack 03).jpg;_25 Trips (front cover).jpg

fieldquery(Type:Folder, Audio:[Filename (path)], Name, 1, 0)
Beautifully Out Of Place;Middle Of The Woods;How Long;25 Trips;Ceiling To The Floor;The Last Minute;Escape;Poison;Waiting;Everybody's Talking;Envy;Less;Father Time

[Last Played]
6/9/2021 3:54 pm

ConvertDate([Last Played])
44356.6624999999985448

Expressions/returns for Last Played date which do not work ok?:

Returns a list as expected, but very large integers (billions value).  Bug??:
fieldquery(Type:Folder, Audio:[Filename (path)], Last Played, 1, 0)
1623269013;1622935860;1619670197;1619670443;1619670672;1619670859;1619671055;1619671263;1619671529;1619671674;1619671822;1619672074

ListMath() to find largest of these very large integers returns the first value in the list, which is ok.
ListMath(fieldquery(Type:Folder, Audio:[Filename (path)], Last Played, 1, 0), 1)
1623269013

Hendrik:
Media Center has two types of date fields, for historical reasons mostly. Either the "Date (Float)" type, or the "Date" type. The "Date" type is a unix timestamp, a very common date format in computers. As far as I can see its used for all playback/tagging related date fields.

Unix timestamps have one downside though, they cannot express dates before 1970, which is fine for the majority of dates that correspond to actual user actions, like "last played", but not for some other Dates.
Hence the second version being created.

The float date is actually in the minority and only used for the actual "Date" field, "Date (release)", and one TV related date field.

So this is not a bug.

markf2748:
@Hendrik:
Thanks for the clarification re date formats.

Is MC equally adept and robust at sorting on both types of dates?  Here's why I ask:

I use the last of the above expressions (ListMath(...)) to put the largest integer Last Played date in a custom expression field called LAST PLAYED AUDIO.
Then in my Last Played Image View, I do a descending custom sort on this field to display the image files in the same order as their corresponding Last Played Audio files from the same music folder.

When it works, it works as expected (looks great) but is rather slow to refresh, whereas my other date sorts are essentially instantaneous.
When it doesn't work, it crashes MC 27.

At the moment, it's crashing and I don't know why, but it is definitely due to sorting on LAST PLAYED AUDIO in the Image view.

zybex:
Sorting by Last Played isn't the problem, that's very fast - faster in fact than sorting by the float date format.
The problem is that you're sorting by an expression that uses FieldQuery. Contrary to most other functions that only operate on the fields of the current file, the FieldQuery() and the Group*() functions need to do a full scan of your entire library to find matching files, each time they are executed.

So when you sort 1000 files by [Last Played], MC reads that field for each file and sorts the list - that's 1000 DB reads;
When you sort by your expression however, the expression is executed [at least] 1000 times and for each execution MC needs to scan all of your 1000 files for matches - that's 1000x1000 = 1.000.000 DB reads, so it's [at least] 1000 times slower than sorting by a field or by an expression that doesn't use FieldQuery.

That's O(n) vs O(n2) in a nutshell. It only gets worse as your collection grows - for 5000 files, it's now 5000 vs 25 million DB reads just to sort the view.

The GroupQuery functions do some caching of results and only re-execute once per minute; I'm not sure if FieldQuery does the same, but even if it does it's gonna be too slow for what you want to do.

markf2748:
@zybex
Thanks for explaining the O(n) vs O(n2) issue, a show-stopper.

Today I tried this custom field expression for my LAST PLAYED AUDIO:

ConvertDate(GroupSummaryQuery(album, last played, 1))

It sorts as desired for Audio and Image Views.  The two views are kept in sync since the field value changes everywhere after playing any audio track or "playing" (i.e. viewing) any side file image.  Unfortunately it has 1-4 seconds refresh time for approximately 500 albums and 5000 tracks, just barely tolerable for me and this relatively small DB.

[updated]

Navigation

[0] Message Index

[#] Next page