SongData.LastPlayed

by **Bex** » Fri Feb 01, 2008 6:37 pm

Great, glad we sorted it all out!

by **Teknojnky** » Fri Feb 01, 2008 4:03 pm

yes, I edited in the quote above, might have missed it

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

YAY more appropriate results!

Thanks Bex, wow that was confusing because I thought for sure it was right but it was the timezone difference messing it up.. LOL.

Both your examples now work, as does Sbondi's once timezone accounted for.

by **Bex** » Fri Feb 01, 2008 3:55 pm

Ok it was very easy. Local time =

Code: Select all

SELECT DateTime('now','localtime') FROM (SELECT 1)

So the two sql's should be:

Code: Select all

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

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','localtime','-6 Hours')-2415018.5))

Where the second one does what you want!
I think I need to update my PlayHistory script now....

by **Bex** » Fri Feb 01, 2008 3:50 pm

That's it 'Now' in SQLite is GMT. Now when we talk about it I remember that I read something about it on their site. I'll check it again and get back soon.

by **Teknojnky** » Fri Feb 01, 2008 3:49 pm

Bex wrote:You actually found something there. Now should read your computer time but test 'now' with this SQL:
Code: Select all
SELECT DateTime('now') FROM (SELECT 1)
It actually was one hour wrong on my computer?

# DATETIME('NOW')
01 2008-02-01 20:48:39

local time = 14:48

by **Bex** » Fri Feb 01, 2008 3:48 pm

You actually found something there. Now should read your computer time but test 'now' with this SQL:

Code: Select all

SELECT DateTime('now') FROM (SELECT 1)

It actually was one hour wrong on my computer?

by **Teknojnky** » Fri Feb 01, 2008 3:42 pm

i wonder..

julianday('now') = GMT?

julianday('now','-6 hours') = CST?

So then I need

julianday('now','-12 hours') = 6 hours earlier cst?

I think mm stores date as local time, where 'now' gives GMT?

from the sqlite link

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

so it seems thats the problem?

by **Bex** » Fri Feb 01, 2008 3:39 pm

The 5) happens for me, to but it's unimportant. The last query displays that the LastPlayed number is higher than the 5) field which should mean that the two SQL I wrote just must work. I don't get it?

by **Teknojnky** » Fri Feb 01, 2008 3:34 pm

Bex wrote: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

The column title has 5) for the julianday in the html.. duno if that is a script html problem or qry problem?

# ARTIST SONGTITLE LASTTIMEPLAYED 5) DATETIME
01 Free All Right Now 39479.603586782 39479.6062919218 2008-02-01 14:29:09
02 Bad Company Movin' On 39479.600706806 39479.6062886235 2008-02-01 14:25:01
03 Foreigner Hot Blooded 39479.598400718 39479.6062888894 2008-02-01 14:21:41
04 Journey Wheel In The Sky 39479.595390625 39479.6063014823 2008-02-01 14:17:21
05 Foreigner Cold As Ice 39479.592743704 39479.6062882068 2008-02-01 14:13:33
06 Golden Earring Radar Love 39479.590484664 39479.6062889244 2008-02-01 14:10:17
07 Grand Funk Railroad We're an American Band 39479.586068993 39479.6062889244 2008-02-01 14:03:56
08 The Doobie Brothers China Grove 39479.583491956 39479.6062884498 2008-02-01 14:00:13
09 The Allman Brothers Band Ramblin' Man 39479.581266331 39479.6063016672 2008-02-01 13:57:01
10 The Doobie Brothers Black Water 39479.57757228 39479.6062879637 2008-02-01 13:51:42

as a side note, I am using vista x64 at the moment, I wonder if there is some funky time issues with vista and/or x64?

by **Bex** » Fri Feb 01, 2008 3:33 pm

You need to adjust the difference with 2415018.5

by **Teknojnky** » Fri Feb 01, 2008 3:30 pm

does this look strange?

Code: Select all

SELECT Artist, SongTitle, julianday(LastTimePlayed), julianday('now')  FROM Songs WHERE (julianday('now') - (julianday(LastTimePlayed) +2415018.5 )) < 1
order by lasttimeplayed desc

# ARTIST SONGTITLE JULIANDAY(LASTTIMEPLAYED) JULIANDAY('NOW')
01 Free All Right Now 39479.603586782 2454498.35428228
02 Bad Company Movin' On 39479.600706806 2454498.3542811
03 Foreigner Hot Blooded 39479.598400718 2454498.35428119
04 Journey Wheel In The Sky 39479.595390625 2454498.35428557
05 Foreigner Cold As Ice 39479.592743704 2454498.35428095
06 Golden Earring Radar Love 39479.590484664 2454498.3542812
07 Grand Funk Railroad We're an American Band 39479.586068993 2454498.3542812
08 The Doobie Brothers China Grove 39479.583491956 2454498.35428104
09 The Allman Brothers Band Ramblin' Man 39479.581266331 2454498.35428563
10 The Doobie Brothers Black Water 39479.57757228 2454498.35428086

These are songs just played.. shouldn't the 'now' field be nearly the same as the last played field?

by **Bex** » Fri Feb 01, 2008 3:25 pm

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

by **Bex** » Fri Feb 01, 2008 3:21 pm

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

by **Teknojnky** » Fri Feb 01, 2008 3:09 pm

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?

by **Bex** » Fri Feb 01, 2008 3:04 pm

Then it works! But what are your local time?

SongData.LastPlayed

Post a reply

Expand view Topic review: SongData.LastPlayed