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

2019-08-12 12:16发布

http://i1345.photobucket.com/albums/p668/Muufley/datacount_zpslqz6ho3i.jpg

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.

标签: sqlite time sum
0条回答
登录 后发表回答