vendredi 10 avril 2015

sqlite: sum time over 24h to HHH:MM:SS

http://ift.tt/1HduQLR


This is a snapshot of my query result. This is the query I used:



SELECT time(sum(strftime('%s', time(TRACKTIME, 'unixepoch'))
- strftime('%s', '00:00:00')), 'unixepoch') AS timesum,
sum(TRACKTIME) AS timesumsec, ARTIST
FROM playcount GROUP BY ARTISTID ORDER BY timesumsec DESC


It's for an sqlite database that collects track length in seconds of music artists. The query sums the total amount of seconds and then converts them to HH:MM:SS. The problem here is once it surpasses the 24h mark (86400 sec) the timesum column zeros the hour value. Ideally I'm trying to display HHH:MM:SS or D HH:MM:SS.


Aucun commentaire:

Enregistrer un commentaire