by phramusca » Mon Apr 02, 2012 7:32 am
Hi,
I am trying to get statistics information from MM.db, but cannot manage to transform your Julian dates into a "YYYY-MM-DD HH:MM:SS" format.
I have tried (at least) the following, but none works. I get weird datetimes (year being -4600 or -1413 where it should be 2012).
SELECT SongPath AS fullPath,
datetime(LastTimePlayed),
datetime(LastTimePlayed, 'localtime'),
datetime((julianday(LastTimePlayed) - 2440587.5)*86400.0, 'unixepoch', 'localtime'),
datetime((LastTimePlayed - 2440587.5)*86400.0, 'unixepoch')
FROM Songs ORDER BY LastTimePlayed DESC
LIMIT 0,10
Could you let me know what SQL statement I should use to get the dates (LastTimePlayed and DateAdded) in "YYYY-MM-DD HH:MM:SS" format ?
Thanks
Hi,
I am trying to get statistics information from MM.db, but cannot manage to transform your Julian dates into a "YYYY-MM-DD HH:MM:SS" format.
I have tried (at least) the following, but none works. I get weird datetimes (year being -4600 or -1413 where it should be 2012).
SELECT SongPath AS fullPath,
datetime(LastTimePlayed),
datetime(LastTimePlayed, 'localtime'),
datetime((julianday(LastTimePlayed) - 2440587.5)*86400.0, 'unixepoch', 'localtime'),
datetime((LastTimePlayed - 2440587.5)*86400.0, 'unixepoch')
FROM Songs ORDER BY LastTimePlayed DESC
LIMIT 0,10
Could you let me know what SQL statement I should use to get the dates (LastTimePlayed and DateAdded) in "YYYY-MM-DD HH:MM:SS" format ?
Thanks