Last 100... Played, Modified, Added 1.6 (2012-08-13)

[nynaevelan]

You don't have to trouble yourself, now that we've identified where the problem is coming from, I can just select the offending files and have them play overnight, that way they are added to the played table and my Top 100 will be more accurate. It is good to read that the timezone issue is resolved, this is not the only script that was doing this.

Nyn

[Bex]

Yes but if you play the songs overnight they will appear a bit different than they should in the other nodes!

[nynaevelan]

What do you mean?? They should have an incremented playcount and a new last played date right??

Nyn

[Bex]

Well it's not the LastPlayed date that is interesting here, it's the timestamp the actual play gets when it's added to the played table.
Playing it tonight will make the timestamp be at night and on a different day than intended. So the 'Per Weekday' and 'Per Period of Day' -Nodes will be slightly incorrect also the 'Chart per Week' will be incorrect if we have entered a new week compared to the intended timestamp.

[nynaevelan]

Oh ok, I understand what you mean, you are referring to the timezone problem that you have to fix. But aren't they all incorrect since I am not in the GB timezone? Since they are not in the play table, doesn't this mean they are not being counted in the statistics? And if so, playing them tonight will add them to the stats right? So, they will be a little off this week, not a major problem, I want them counted. Besides I probably have some inaccuracies in my stats anyway since there are several days when I leave specific tracks playing overnight so that Minilyrics can find the lyrics and I'm sure I have other 'editing' inaccuracies.

Nyn

[Bex]

Actually no, the timezone problem is a completely different issue which is not related to what I'm trying to explain.

If you use the 'Set Playcounter Script' or sync the playcount from a portable device or import your playcount from another MediaPlayer. The played table will not be updated with any information at all.

The played table contains one record of each and every time a song have been played within MM, with the exact timestamp when the songs was played. My PlayHistory Script takes that info and groups it into the nodes you see in the script.

However if you try to add records ti the played table by playing them "over night" then they will get the wrong timestamp compared to when they were actually played thus making the statistic slightly incorrect. Not a big thing though. I just wanted to explain how it works.

[nynaevelan]

Ok, I think I understand now, I think I wasn't understanding what you meant because I was assuming the played table only stored the last time a track was played, not each time. Thanks for the explanation.

Nyn

[ZvezdanD]

I currently looking into the PlayHistory script now due to some newly discovered behaviour of how SQLite defines 'now'. It's considered as now in GMT so the script doesn't work properly unless you live in GB. It's most notable in the Played Today/Played Yesterday -Nodes which actually you reported some time ago. It's fixed now so I'll see if I easily can change the Top 100 nodes as well.

I think you should use date('now', 'localtime') instead of date('now'). Also state for julianday.

Value 2415018.5 also shouldn't be used (at least not always), but 2415019.0, which is a result of julianday('1899-12-30 12:00'):

"The julianday() function returns the number of days since noon in Greenwich on November 24, 4714 B.C."

[Bex]

Thanks ZvezdanD.

Me and tekno came to that conclusion yesterday:
http://www.mediamonkey.com/forum/viewtopic.php?t=25687

Regarding Julianday
I just calculated backwards and got the figure 2415018.5. I'll investigate this further.

[ZvezdanD]

Me and tekno came to that conclusion yesterday

Maybe you came to the same conclusion, but your solutions are wrong. You should specify 'local' if you want to take in account the local time zone.

Regarding Julianday
I just calculated backwards and got the figure 2415018.5.

I thought for long time that it should be like that, but after more in dept testing I concluded that it should be 2415019.0 (e.g. julianday('1899-12-30 12:00')), but it is not necessary always. For example, if expression contain 'now', it should be with 2415019.0.

This is obvious if you take a look on SQLite definition for julianday - you get full day in the noon, not in the midnight.

[ZvezdanD]

I am sorry. I saw just now that mentioned thread has a second page where you suggested 'localtime'.

[Bex]

Yes that was our solution!

Regarding Julianday
I just calculated backwards and got the figure 2415018.5.

I thought for long time that it should be like that, but after more in dept testing I concluded that it should be 2415019.0 (e.g. julianday('1899-12-30 12:00')), but it is not necessary always. For example, if expression contain 'now', it should be with 2415019.0.

This is obvious if you take a look on SQLite definition for julianday - you get full day in the noon, not in the midnight.

Hmm..
If i use 2415019.0 then I get a mismatch with 12 hours
If i use 2415018.5 then I get it right.
What am I missing?

[ZvezdanD]

Yes that was our solution!

Magic Nodes had this from v1.6.

If i use 2415019.0 then I get a mismatch with 12 hours
If i use 2415018.5 then I get it right.

It depends from situations and how you consider a full day. For example, let say that I add a track in 10 AM today. When will I get one day since added: tomorrow in 00:00 or in 10 AM? Or maybe one day after tomorrow in 00:00? Same question could be asked for your Last week and Last month (but not for Previous week and Previous month). I consider that a full day is from 00:00 until next day in 00:00, so for me one day since added is one day after tomorrow from 00:00. Your script calculates full day from one point in time to this time + 24:00, e.g. in previous example it is tomorrow from 10 AM. But never mind, forget about this - it is questionable if my approach is anything better. Maybe I will return my calculations to 2415018.5.

BTW, I know this goes from this topic, but I think you have an error in PlayHistory3.6.1 script in the the line 867: instead of cbxPrevMonth I suppose it should be cbxPrevWeek.

Also, you have too many Casts to intiger, instead of integer. I am wondering how SQLite works with that - you could type a Cast to anything, it don't have error checking for this at all.

[Bex]

It depends on how you define one day. Is it 24 hours or when the calender changes day?

Thanks for the bug report it was actually wrong on every place where cbxPrevWeek should have been.

Regarding the cast thing. It's due to I want the date without time. I don't see how I can change it?

[ZvezdanD]

Regarding the cast thing. It's due to I want the date without time. I don't see how I can change it?

Maybe you didn't understand me - you mistyped intiger instead of integer. Just replace all such appearances.

If you want just date part of some date/time value, you could use date() function. I see you are using it extensively inside of your script, so I don't understand where is a problem. Maybe you need decimal representation? Then you should use julianday(date()). For example, julianday(date('2008-02-04 08:00')) will return same result as julianday(date('2008-02-04 18:00')), i.e. 2454500.5. But, if you use cast, you will get two different values for a same day: 2454500 for cast(julianday('2008-02-04 08:00') as integer) (time before the noon) and 2454501 for cast(julianday('2008-02-04 08:00') as integer) (time after the noon).