SongData.LastPlayed

[Teknojnky]

Was wondering if anyone could update this property in the wiki..

http://www.mediamonkey.com/wiki/index.p ... LastPlayed

specifically, I am looking for info on how to use LastPlayed to do somethign like..

If SongData.LastPlayed < 1 (minute or hour or day or month or year, etc) Then
do something
End If

Doing that directly like above obviously doesnt work, so what would be the best way to manage that?

I didn't see any relevant examples from google, but I am not sure I was searching right terms.

Do the sql queries work in the same fashion?

Any help or pointers appreciated.

[sbondi]

Was wondering if anyone could update this property in the wiki..

http://www.mediamonkey.com/wiki/index.p ... LastPlayed

specifically, I am looking for info on how to use LastPlayed to do somethign like..

If SongData.LastPlayed < 1 (minute or hour or day or month or year, etc) Then
do something
End If

Doing that directly like above obviously doesnt work, so what would be the best way to manage that?

I didn't see any relevant examples from google, but I am not sure I was searching right terms.

Do the sql queries work in the same fashion?

Any help or pointers appreciated.

Hi,

The MM3 database field "LastTimePlayed" in the Songs table is in a VB-type date/time format which stores the completed days since 1899-12-30 to the left of the decimal point, and the completed fraction of time since midnight to the right of the decimal point. If it is unassigned (i.e., the song was never played in MM3), then the value is equal to 0.0.

In any case, to work with SQLite date functions, you will need to convert this VB-type date to a true "julianday" by adjusting for "1899-12-30":

Code: Select all

(julianday(LastTimePlayed) + julianday('1899-12-30'))

Then, consider the special SQLite "now" julianday:

Code: Select all

julianday('now')

You can then do date "math" comparisons ...

1. This will query tracks that were played within the last two days:

Code: Select all

SELECT * FROM Songs WHERE (julianday('now') - (julianday(LastTimePlayed) + julianday('1899-12-30'))) < 2

2. This will query tracks that were played within the last 45 seconds:

Code: Select all

SELECT * FROM Songs WHERE (julianday('now')*86400 - (julianday(LastTimePlayed) + julianday('1899-12-30'))*86400) < 45

Hope this helps,

Steve

[Teknojnky]

Cool thanks, I think the sql stuff will help later on.

The MM3 database field "LastTimePlayed" in the Songs table is in a VB-type date/time format which stores the completed days since 1899-12-30 to the left of the decimal point, and the completed fraction of time since midnight to the right of the decimal point. If it is unassigned (i.e., the song was never played in MM3), then the value is equal to 0.0.

Here tho, the songdata.lastplayed time is not the database field, but an object of 'Date'.

Which when sent to a msgbox(SongData.LastPlayed) is formated as:

6/9/2007 9:02:44 AM

So.. I'm assuming at this point that the only way to calculate the difference between that date and 'now' would be to perform various string manipulation to extract the relevant fields from each 'date' ?

or is there an easier way figure out how to determine if that date numerically > than x amount of hours/days/months/years since then?

[Bex]

Simply use DateDiff. The LastPlayed is in DateTime Format (even if it's presented in another way).

http://www.w3schools.com/vbscript/func_datediff.asp

This gives you elapsed seconds between now and LastPlayed:

Code: Select all

Sub test
   Dim list,i
   Set list = SDB.SelectedSongList
   For i = 0 to 0 'list.Count
      info = info & "LastPlayed: " & list.item(i).LastPlayed  & VbNewline
      info = info & "Now: " & Now() & VbNewline
      info = info & "Diff: " & DateDiff("s",list.item(i).LastPlayed ,Now())
   Next
   SDB.MessageBox Info, mtInformation, Array(mbOK)
End Sub

[sbondi]

Cool thanks, I think the sql stuff will help later on.

The MM3 database field "LastTimePlayed" in the Songs table is in a VB-type date/time format which stores the completed days since 1899-12-30 to the left of the decimal point, and the completed fraction of time since midnight to the right of the decimal point. If it is unassigned (i.e., the song was never played in MM3), then the value is equal to 0.0.

Here tho, the songdata.lastplayed time is not the database field, but an object of 'Date'.

Which when sent to a msgbox(SongData.LastPlayed) is formated as:

6/9/2007 9:02:44 AM

So.. I'm assuming at this point that the only way to calculate the difference between that date and 'now' would be to perform various string manipulation to extract the relevant fields from each 'date' ?

or is there an easier way figure out how to determine if that date numerically > than x amount of hours/days/months/years since then?

Oh, sorry I misunderstood. So, you are writing a script that is referring to the SongData object. This is even better, since this is formatted as a VB-type date and the world of VBScript is at your disposal.

BTW, the VBScript MsgBox is flexible and displays it as a formatted string, but internally it is truly a Date value that can be used for any date operations.

Are you familar with date operations in VBScript?

Code: Select all

If (DateDiff("s", SongData.LastPlayed, Now) < testNumInSeconds) Then

Note I set the first parameter to "s" to evaluate "seconds", but it can be either:

"yyyy" for Year
"q" for Quarter
"m" for Month
"y" for Day of year
"d" for Day
"w" for Weekday
"ww" for Week of year
"h" for Hour
"n" for Minute
"s" for Second

[Teknojnky]

Awesome, thats just what I need.. I saw something about datediff when I searched, but I assumed (wrongly) that the date was stored as displayed instead of compatible with datediff.

Thanks Bex & Sbondi!

edit: I put a link to this thread in the wiki for this property.. hopefully it will be enlightening for other scripters in the future.

[Teknojnky]

This qry should return list if artist played a track in last 6 hours, right?

Code: Select all

SELECT Songs.Artist, Songs.LastTimePlayed
FROM Songs
WHERE Songs.Artist like 'poison' AND (julianday('now')*24 - (julianday(LastTimePlayed) + julianday('1899-12-30'))*24) < 6
ORDER BY Songs.LastTimePlayed DESC

this isnt working either.. 600 seconds = 10 minutes, and its not returning any tracks

Code: Select all

SELECT Songs.Artist, Songs.SongTitle, Songs.LastTimePlayed
FROM Songs
WHERE (julianday('now')*86400 - (julianday(LastTimePlayed) + julianday('1899-12-30'))*86400) < 600
ORDER BY Songs.LastTimePlayed DESC

*note: using the above queries in bex's sql viewer script, if those are supposed to be right, perhaps a problem with script?

------------------------------------------------

Ok, well I tried it in function and seems to work maybe..

Code: Select all

Function Prune(inSongList) 'takes a songlist and returns songlist with only desirable tracks'
  'probly temporary until a better qry can be determined'
  Dim outSongList : Set outSongList = SDB.NewSongList
'   msgbox(insonglist.count) 
  If Not (inSongList is Nothing) Then
    If inSongList.Count > 0 Then
      Dim x, Iter, QRY : x = 0
      QRY = "SELECT Songs.Artist, Songs.LastTimePlayed" &_
        " FROM Songs WHERE Songs.Artist like '" & inSongList.Item(x).ArtistName &_
        "' AND (julianday('now') - (julianday(LastTimePlayed)" &_
        " + julianday('1899-12-30'))) < .25" &_
        " ORDER BY Songs.LastTimePlayed DESC"
  '     msgbox("lastplayed: " & inSongList.Item(x).LastPlayed)
      For x=0 to inSongList.Count-1
        Set Iter = SDB.Database.OpenSQL(QRY)
        SDB.ProcessMessages
        If Iter.EOF = True Then 'we don't want any results'
          SDB.ProcessMessages
          If NOT inSongList.Item(x).Rating < DJMinRating Then
            If NOT (DateDiff("d", inSongList.Item(x).LastPlayed, Now) < DJMinDays) Then
              outSongList.Add inSongList.Item(x)
            'ElseIf inSongList.Item(x).Genre contains DJAvoidGenres Then
            'Else whatever else I forgot
            End If
          End If
        Else
          msgbox("Skipped artist: " & inSongList.Item(x).ArtistName)
        End If
        SDB.ProcessMessages
      If outSongList.Count >= DJMaxCount Then
        Exit For
      End If
      Next
    Else
      msgbox("insonglist.count was zero")
    End If
  Else
    msgbox ("Prune did not get valid songlist")
  End If
  Set Prune = outSongList
'   msgbox(prune.count)

End Function

Strange?

edit again, actually I don't think its working, because it is supposed to prompt me when a repeat too soon artist is skipped.. and its not prompting and still getting repeats..

[Bex]

This should work:

Code: Select all

SELECT Songs.Artist, Songs.SongTitle, Songs.LastTimePlayed, DateTime(LastTimePlayed+2415018.5) DateTime
FROM Songs WHERE LastTimePlayed > (julianday('now','-6 Hours')-2415018.5)

But I think it would be best to return the Artist in the Artists Table to cater for multiple artist assignments. This is how to do that:

Code: Select all

SELECT Artists.Artist FROM ArtistsSongs, Artists
WHERE Artists.ID=ArtistsSongs.IDArtist AND PersonType=1 AND IDSong IN 
(SELECT ID FROM Songs WHERE LastTimePlayed > (julianday('now','-6 Hours')-2415018.5))

Here's my understanding of converting MM DateTime back and forth to real DateTime.

Real DateTime = DateTime(MMDate+2415018.5)
MMDate = julianday('Real DateTime')-2415018.5

Read more about SQLite date/time functions here:
http://www.sqlite.org/cvstrac/wiki?p=Da ... eFunctions

[Teknojnky]

Both those qry are supposed to return a list of any that play in the last 6 hours?

I get no results for either (using sqlviewer) and I have definately have tracks played during then.

It would be great if MM had an SDB.Artist.LastPlayed time, then I could just use the datediff() there too.

[Bex]

Yes and they work fine for me. What do you get with this query:

Code: Select all

SELECT Songs.Artist, Songs.SongTitle, Songs.LastTimePlayed, DateTime(LastTimePlayed+2415018.5) DateTime
FROM Songs ORDER BY LastTimePlayed DESC

[Teknojnky]

rows 129785

# ARTIST SONGTITLE LASTTIMEPLAYED DATETIME
01 Steve Miller Band Take the Money and Run 39479.574699039 2008-02-01 13:47:33
02 Lynyrd Skynyrd Sweet Home Alabama 39479.563872662 2008-02-01 13:31:58
03 Eagles Life In The Fast Lane 39479.560628206 2008-02-01 13:27:18
04 Eric Clapton I Shot the Sheriff 39479.557346076 2008-02-01 13:22:34
05 Eagles Hotel California (live) 39479.5528842014 2008-02-01 13:16:09
06 Queen Bohemian Rhapsody 39479.545342685 2008-02-01 13:05:17
07 Led Zeppelin Immigrant Song 39479.541284699 2008-02-01 12:59:26
08 The Who Pinball Wizard 39479.539638171 2008-02-01 12:57:04
09 Led Zeppelin Black Dog 39479.537575162 2008-02-01 12:54:06
10 The Who Won't Get Fooled Again 39479.531411493 2008-02-01 12:45:13

[Bex]

Then it works! But what are your local time?

[Teknojnky]

1400 central (us)

But the other 2 qry does not..

and this query does work for .3 but does not work for .25

Code: Select all

SELECT Artist, SongTitle  FROM Songs WHERE (julianday('now') - (julianday(LastTimePlayed) + julianday('1899-12-30'))) < .3

# ARTIST SONGTITLE
01 Queen Bohemian Rhapsody
02 Steve Miller Band Take the Money and Run
03 The Doobie Brothers Black Water
04 The Doobie Brothers China Grove
05 Eagles Hotel California (live)
06 Eric Clapton I Shot the Sheriff
07 Grand Funk Railroad We're an American Band
08 Eagles Life In The Fast Lane
09 Led Zeppelin Immigrant Song
10 Lynyrd Skynyrd Sweet Home Alabama
11 The Who Pinball Wizard
12 The Allman Brothers Band Ramblin' Man

Code: Select all

SELECT Artist, SongTitle  FROM Songs WHERE (julianday('now') - (julianday(LastTimePlayed) + julianday('1899-12-30'))) < .25

No results

.26 is the lowest I can go and get results..

# ARTIST SONGTITLE
01 The Doobie Brothers China Grove
02 Grand Funk Railroad We're an American Band
03 The Allman Brothers Band Ramblin' Man


---------------------------------
well I got to .255

.255 * 24 (hours) = 6.12

I thought that was supposed to be 6 hours

maybe I'm retarded and that is 6 minutes?

[Bex]

I don't understand why it doesn't work for you. Are you sure that you copied the codes exactly as they are?

[Bex]

Try this:

Code: Select all

SELECT Songs.Artist, Songs.SongTitle, Songs.LastTimePlayed, (julianday('now','-6 Hours')-2415018.5)
,DateTime(LastTimePlayed+2415018.5) DateTime
FROM Songs ORDER BY LastTimePlayed DESC