Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[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(n²) 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(n²) 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]

[markf2748]

In the hope of reducing the above O(N²) expression algorithm to O(N), I will first describe how it works in more detail:

(1) An audio track plays or an image is viewed.

(2) MC automatically updates field LAST PLAYED for the active file.

(3) GroupSummaryQuery() sets my custom field LAST PLAYED AUDIO to that last played value for every audio and image file in the played album.  This works because of the unique way GroupSummaryQuery() handles LAST PLAYED.  It actually finds the most recent LAST PLAYED value across all files in the matched album, and then returns that same value for all files in the album!  This is exactly the required functionality, but unfortunately it is O(N) since GroupSummaryQuery() looks for matches across the entire library each time it is called. 

(4) Sorting my audio and image views on LAST PLAYED AUDIO, the displayed album order is synchronized between the views.  Mission accomplished, except the entire process is O(N²) as explained above by zybex.

I don’t believe there is any intrinsic reason why step (3) needs an O(N) function.  I think using an expression that returns the newest (largest) value of LAST PLAYED for its album argument, testing both audio and image files in the album, should be O(1).
 
After spending more than a day pouring through Expression Language documentation and threads, I have no success in beating down Step (3).  Any ideas?  Is a new function needed?

Thanks.

[marko]

A lot of what you are doing is way, way, over my head, so forgive me if what follows is miles off target...

If you want the largest value from a date, could you somehow use Max(FormatDate([date,0],yyyyMMdd)) to return the date as a number and then return the largest value?

I hope you found the Expression Language pages helpful. A lot of time has been poured into those

-marko

[zybex]

(3) GroupSummaryQuery() sets my custom field LAST PLAYED AUDIO to that last played value for every audio and image file in the played album.

The thing is, expression fields don't have any stored value - they're recalculated on each access to the field. So when you play a file your [Last Played Audio] isn't updated or even evaluated; when you open your View and MC needs to sort it, THAT's when the value of that field is calculated, for each and every file in the View.

What you need is a Relational Field, one value per Album/ArtistAlbum, to store this [Album Last Played]. Unfortunately, while you can create such a field, it won't get populated automatically when you play a file. Perhaps you can ask for this field as a new feature.

[markf2748]

The thing is, expression fields don't have any stored value - they're recalculated on each access to the field. So when you play a file your [Last Played Audio] isn't updated or even evaluated; when you open your View and MC needs to sort it, THAT's when the value of that field is calculated, for each and every file in the View.

I am not sure about the actual internal lifetime of an expression field value.  But I do know that if I display such a field in the tag panel, the display shows the last calculated value.  In any case, the current behavior is perfectly fine:  I only need LAST PLAYED AUDIO when I open a view and thereby induce an automatic refresh before the automatic sort, or if I manually manually refresh (F5) out of curiosity. 

What you need is a Relational Field, one value per Album/ArtistAlbum, to store this [Album Last Played]. Unfortunately, while you can create such a field, it won't get populated automatically when you play a file. Perhaps you can ask for this field as a new feature.

I agree, a new Relational Field would be an efficient and convenient solution.  Alternatively, or in addition, a new O(1) function that returns the value, as described above, would work perfectly fine in a custom Expression Field.  After all, I have watched GroupSummaryQuery() work exactly in this manner for what seems like hundreds of times by now.

Re names:  My field LAST PLAYED AUDIO is a poor choice.  I would vote for LAST PLAYED (ALBUM) or in second place your ALBUM LAST PLAYED.
The new function might be LastPlayed(Album) or some such thing.

Other note:  For most of the cases I've checked so far, the flac file and jpeg image dates in LAST PLAYED AUDIO are identical to all displayed digits for the same album.  However I ran across an album last night where the dates are the same only up to about the third decimal place.  That is adequate for my album sorting purposes, but I don't yet know what causes this difference.  SEE FOLLOWUP BELOW

Thanks for your continued interest.

[zybex]

If the numbers are different, then it's a different date. The value represents the number of seconds since 1970. You can convert it to a date here:
https://www.epochconverter.com/

GroupSummaryQuery() and fieldQuery() work in a similar way. Both of them need to scan through all your collection, each time they are executed, to find the related/wanted files and then extract the needed values from them. Both of them are similar in speed. What can be done (and is done for GroupSummaryQuery) is to cache some results and only refresh them periodically - this works and improves performance a bit, but can also give you wrong instant results. It's a compromise, there is no shortcut or magic bullet.

If you create a View based on GroupSummaryQuery(), you'll notice that it takes a bit to load, then works very fast for about 1 minute. Then it stutters again while it recalculates, then works again for 1 minute. That's the best you can expect for now, using these functions.

[markf2748]

If the numbers are different, then it's a different date. The value represents the number of seconds since 1970. You can convert it to a date here:
https://www.epochconverter.com/

The sortable date returned by my Expression Field ConvertDate(GroupSummaryQuery(album, last played, 1)) is in MC's float format, so the Unix format converter does not apply.  I've now added another custom field without the ConvertDate() call which shows up in the tag panel as human readable.  Very helpful.

If you create a View based on GroupSummaryQuery(), you'll notice that it takes a bit to load, then works very fast for about 1 minute. Then it stutters again while it recalculates, then works again for 1 minute. That's the best you can expect for now, using these functions.

Yes, I noticed the variable update delays when refreshing.  Independent of these, I find it annoying that the built-in LAST PLAYED field only updates at the very end of the playing track (or when stopped after 50% play and then you start another track).  Personally, I prefer to have it automatically update after a minute or so of audio play without stopping playback.  A custom setting would be nice.

Re the problem "Other note" in my previous post:  All is well after fixing a spelling inconsistency in filenames and related fields.  The smartlist All Files (empty search) was very helpful in tracking this down .

[markf2748]

Good news update: A new field [Last Played (album)] is promised for MC 28:

https://yabb.jriver.com/interact/index.php/topic,129719.0.html

That should reduce this Last Played synchronization problem to a simple, high speed view sort like any other.
Worst case, you may need a new library field with the expression ConvertDate([Last Played (album)]) to make it sortable .

I hope you found the Expression Language pages helpful. A lot of time has been poured into those

Very helpful indeed, essential to pursuing this topic.  Soon there will be a few more pages to update!

[markf2748]

Good news update: A new field [Last Played (album)] is promised for MC 28:

https://yabb.jriver.com/interact/index.php/topic,129719.0.html

That should reduce this Last Played synchronization problem to a simple, high speed view sort like any other.

Works fantastic in the new MC28.0.25!  Tested synchronization between a Last Played Audio View and a Last Played Image View.
Simply specify in the view tab menus: Sort By > Custom > Descending (z-a) on "Last Played (album)".
The field also displays in human readable form by default when added to a tag window.

Notes:
As usual, a track's last played time does not update until it has completed, or else until it has played at least 50% thru and a new play starts.
The view may also need a Refresh (button or F5) to see very latest state when system is in between its internally scheduled updates (they occur approximately every minute).