SongData.LastPlayed

[Teknojnky]

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?

[Bex]

You need to adjust the difference with 2415018.5

[Teknojnky]

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?

[Bex]

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?

[Teknojnky]

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?

[Bex]

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?

[Teknojnky]

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

[Bex]

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.

[Bex]

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....

[Teknojnky]

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

Code: Select all

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.

[Bex]

Great, glad we sorted it all out!