@ Barry4679 Thanks for your comments (I missed a comment from the MM5 makers)

I did some more tests and found that the Played table does not give useful results. Not only that data and times are not always correct, but also how often the song was played (Played#). There are big inconsistencies. So there are big deviations e.g. Played# = 56 but in the table there are only 29 entries. As a conclusion one must say this table is not usable. So there is still a lot of potential left at least for the "played history".

Now to say something positive:
nevertheless MMW is for me the best music database (according to my limited knowledge). I also like the new concept of MM5. Thank you for the valuable work. I use MMW almost daily.

[quote=sonos post_id=492943 time=1641558909 user_id=68003]
What is the deeper reason why users can set the Played# date?
[/quote]

I doubt that there is any deep reason, other than it is a non-static non-critical attribute that you you can "correct" if you see the need.

Played Date in MM5 is just an informed guess, because MM cannot see plays that you make with any tool that is not MMW or MMA, and as Ludek has informed us, even MMA is unable to see collect dates from some wretched Apple devices for some reason.

MM doesn't really "collect" play history as such. I see the "data" in the Played table as being some workings that they didn't throw away, just in case they are of use to any Addon developers in the community, rather than any serious attempt to collect all your play history.

Evidence is:
[list]the data in the Played table is not visible in MM, nor is it easily exported from MM[/list]
[list]the database has no referential integrity measures to keep the visible Played stats in sync with the Played table[/list]
[list]and, and you say, they offer facility to alter the "answer" without affecting the components from which it was built[/list]

What is the deeper reason why users can set the Played# date?

Another oddity is:
That [b]Added [/b] date/time after ripping a CD is for the first track 2021/03/02 [b]12:28[/b] and for track 2 2021/03/02 [b]12:29[/b]. This seems reasonable.
However according to query (see above) for track 1 2021/03/02 [b]20:37[/b] and for track 2 2021/03/02 [b]17:31[/b].
The date is correct but the times are very different and the order is reversed.
When will the PLAYDATE field be updated?

Hi,
just reading this thread and I can confirm that Songs.playCounter value does not correspond to the sum in the Played table.

The reasons are:
1) Played # is editable field in MM5 so users can set any integer value as they like
2) The play count is synced with devices like iPods where we don't know the exact history, but just how many times the track has been played in iPod from the last sync operation (to increase in MM5)

[quote]Peke wrote
Hi,
Are you sure that Played # was not increased during Sync as that way it will not showed in played table when using SQL?[/quote]

I have done a few tests more and do not understand the discrepancies. I have compared the data:
Song added vs. first entry of the query and
Played# count vs listed number of query.
Song added vs. first entry of query and
Played# count vs listed plays of query.

Song ADDED : first date of entry : PLAYED# : listed number of Plays of query
21.09.2011 : 30.10.2011 : 15 : 11
24.04.2011 : 05.08.2012 : 14 : 21
24.01.2020 : 24.01.2020 : 8 : 6
20.04.2021 : 20.04.2021 : 27 : 27
no rule or trend can be observed

SQL query applied:
SELECT Strftime('%Y/%m/%d %H:%M:%S', ( Julianday(playdate) + 2415018.5 ), 'localtime')
AS plays
FROM played AS p
JOIN songs AS s
ON s.id = p.idsong
WHERE album = 'Name of Album'
AND songtitle = Name of song';

title: 'Tracks with Artist same as Title',
sql: 'Songs.artist = Songs.SongTitle'
}, {
title: 'Tracks with Album Artist same as Title',
sql: 'Songs.albumArtist = Songs.SongTitle'
}, {
title: 'Tracks with BPM of 1 or Less',
sql: 'Songs.BPM <= 1'
}, {
title: 'Tracks with Bit Rate of 128k or less',
sql: 'Songs.Bitrate <= 128000'
}, {
title: 'Tracks with No Lyrics',
sql: 'Songs.Lyrics = ""'

Good discussion. Perhaps we can put together a few additions to Ludek's Custom Nodes add-on?

Under Ludek's track node of the files CustomNodesDefintion.js (found in C:\Program Files(x86)\MMW5 Debug\Scripts\Custom Nodes in my setup) I have previously added some simple SQL queries:

[code]title: 'Tracks with Artist same as Title',
sql: 'Songs.artist = Songs.SongTitle'
}, {
title: 'Tracks with Album Artist same as Title',
sql: 'Songs.albumArtist = Songs.SongTitle'
}, {
title: 'Tracks with BPM of 1 or Less',
sql: 'Songs.BPM <= 1'
}, {
title: 'Tracks with Bit Rate of 128k or less',
sql: 'Songs.Bitrate <= 128000'
}, {
title: 'Tracks with No Lyrics',
sql: 'Songs.Lyrics = ""'[/code]

I think it would be useful to have a few nodes to query when the PlayCount is at odds with the LastPlayedDate. Also when there is disparity between the LastPlayedDate and the Play History. But I think these are more complex SQL queries than I am comfortable with!

[quote=sonos post_id=492742 time=1641230486 user_id=68003]
After a few tests I sometimes see inconsistencies, e.g.
the main panel for a selected song and album shows:[b] Played# = 15[/b] with [b]Added date = 2012/09/21[/b].
Whereas the SQL query for this song and album shows only [b]Played# = 13[/b] entries and as first played date [b]2013/08/29[/b].
Obviously some entries have been lost in the last 10 years. :o
[/quote]

Yes, like I said it is a [url=https://www.thefreedictionary.com/Midden+pits]midden pit[/url] ... ie, interesting to archaeologists, so that they can estimate historic track plays, but it is a long way short of being an official census.

They collect the data. They don't use it, and they make no attempt to validate nor protect it.

I have relieved MM from the task of collecting my play stats, somewhat because of the above issue, but mostly because it does not know what I play on my Sonos units, and it does not know what I stream when away from the house.

I have an application that collects the real data, and overwrites the play data in the MM database.

I only care about play count and date last played, because I use these as criteria in my playlists.
But I have also store all the individual plays into MM, because I have it, and one day I may get or make something that makes pretty pictures from the history.

Hi,
Are you sure that Played # was not increased during Sync as that way it will not showed in played table when using SQL?

After a few tests I sometimes see inconsistencies, e.g.
the main panel for a selected song and album shows:[b] Played# = 15[/b] with [b]Added date = 2012/09/21[/b].
Whereas the SQL query for this song and album shows only [b]Played# = 13[/b] entries and as first played date [b]2013/08/29[/b].
Obviously some entries have been lost in the last 10 years. :o

Hi Barry4679
This is exactly what I was looking for!

Thanks a lot :D

SELECT Strftime('%Y/%m/%d %H:%M:%S', ( Julianday(playdate) + 2415018.5 ),
       'localtime')
       AS plays
FROM   played AS p
       JOIN songs AS s
         ON s.id = p.idsong
WHERE  album COLLATE nocase = 'Citizen of Glass'
       AND songtitle COLLATE nocase = 'Trojan Horses';

SELECT Strftime('%Y/%m/%d %H:%M:%S', ( Julianday(playdate) + 2415018.5 ),
       'localtime')
       AS plays
FROM   played AS p
       JOIN songs AS s
         ON s.id = p.idsong
WHERE  album = 'Citizen of Glass'
       AND songtitle = 'Trojan Horses';

[quote=sonos post_id=492685 time=1641148703 user_id=68003]
I was able to [b][color=#FF0040]create a table from the DB[/color][/b] with all PLAYDATE values applying e.g.
SELECT PLAYDATE FROM Played WHERE IDSong = (SELECT ID FROM Songs WHERE Songtitle = 'The Wedding' AND Album ='Kristallen')
or
SELECT PLAYDATE + 2415018.5 FROM Played WHERE IDSong = (SELECT ID FROM Songs WHERE Songtitle = 'The Wedding' AND Album ='Kristallen')

However I'm struggling to '[b][color=#FF0040]feed in' this table[/color][/b] into

SELECT strftime('%Y/%m/%d %H:%M:%S',(julianday([i]PLAYDATE Table[/i]) + 2415018.5),'localtime')

to finally create a table with the individual playdate data (in readable form)
[/quote]

I don't understand exactly what you are trying to achieve, ie what do you mean by " create a table from the DB", and also "feed in this table"?

The last sql fragment I posted only showed how to get the [b]last [/b]play date for a album's track.
You are after all play dates I think.

To achieve that you need to join the Songs table (to get the track or album that you are interested in), and the Played table (to get all of the play dates).

eg:
[code]SELECT Strftime('%Y/%m/%d %H:%M:%S', ( Julianday(playdate) + 2415018.5 ),
'localtime')
AS plays
FROM played AS p
JOIN songs AS s
ON s.id = p.idsong
WHERE album COLLATE nocase = 'Citizen of Glass'
AND songtitle COLLATE nocase = 'Trojan Horses';[/code]

or if you are only running this inside the SQL Editor running in the MM5 addon, it is a bit easier:
[code]SELECT Strftime('%Y/%m/%d %H:%M:%S', ( Julianday(playdate) + 2415018.5 ),
'localtime')
AS plays
FROM played AS p
JOIN songs AS s
ON s.id = p.idsong
WHERE album = 'Citizen of Glass'
AND songtitle = 'Trojan Horses';[/code]

There is a limit to how much automation you can achieve just with SQL.
To achieve automation and operational flexibility you need to call the SQL from inside some programming languauge.

Hi Barry4679

Thanks for your reply!

I was able to create a table from the DB with all PLAYDATE values applying e.g.
SELECT PLAYDATE FROM Played WHERE IDSong = (SELECT ID FROM Songs WHERE Songtitle = 'The Wedding' AND Album ='Kristallen')
or
SELECT PLAYDATE + 2415018.5 FROM Played WHERE IDSong = (SELECT ID FROM Songs WHERE Songtitle = 'The Wedding' AND Album ='Kristallen')

However I'm struggling to '[b]feed in[/b]' this table into

SELECT strftime('%Y/%m/%d %H:%M:%S',(julianday([i][b]PLAYDATE Table[/b][/i]) + 2415018.5),'localtime')

to finally create a table with the individual playdate data (in readable form)

Any hint is gratefully welcome

Hi Sonos .. Hi Ian

yes MM database is a a SQLITE database.
and the PLAYED table contains details of every play, but there needs to be some caution taken before relying on it.
MM does not appear to use this data except for in the Statistics report AFAIKS.
It if a bit like a midden pit where they dump their waste and by-products.

The database appears to have no referential integrity measures that ensure the sum of Played plays bears any relation to the counts in the Songs table. The User Community tools that import external plays into MM, or allow play history corrections, typically ignore the Played table.

MM date formats in the database are a bit of a jumble for historic reasons. The date played fields are in Julian Date format.
SQLITE has function that will deal with Julain Dates: https://www.sqlite.org/lang_datefunc.html

eg.
select strftime('%Y/%m/%d %H:%M:%S',(julianday(LastTimePlayed) + 2415018.5),'localtime')
from songs
where album collate nocase = 'Citizen of Glass'

It is easier to read them, than write them.
I add my Sonos plays, and my away-from-home plays from my Last.fm account, into MM, just for the sake of completeness.
So I now have 340,000 records of bloat sitting in my Played table, accumulated over 12+ years ... but maybe you are creating some purpose for them?

Hi Sonos,

I think you are on the right track:

https://www.mediamonkey.com/forum/viewtopic.php?p=131973#131973

This may also help a little - but does not mention / define UTCOFFSET:

https://www.mediamonkey.com/wiki/Database_Structure_%28MM4%29

And not to forget UTCOFFSET