Convert SongLength into h:min:sec

[sonos]

Assumption: SongLength in the DB table Songs represents the duration of the corresponding song.
How can I convert this integer value SongLength to h:mm:ss?
Query:
SELECT SongLength
FROM songs
WHERE album COLLATE nocase = ''Kristallen''
AND songtitle COLLATE nocase = 'The Wedding';
=>293720
E.g. SELECT time(293720, 'unixepoch' ); does not work
The result should be: 4min 54sec

[sonos]

I get the correct number if I omit the last three digits.
Integer value: 293720
select time(293, 'unixepoch' ); => 4:53
rounded up 720 => 293 +1
select time(294, 'unixepoch' ); => 4:54
I assume that the last 3 digits represent the milliseconds.
How can I include the milliseconds?

[drakinite]

Is there a particular reason you want to do the conversion purely in SQL code instead of in JS code? Are you making an addon?


Edit: Not sure, but one of these might help https://stackoverflow.com/questions/126 ... sing-t-sql
https://stackhowto.com/mysql-how-to-con ... ss-format/
https://stackoverflow.com/questions/268 ... te#2686112
https://stackoverflow.com/questions/242 ... s#24266757

[sonos]

drakenite wroteIs there a specific reason why you want to do the conversion purely in SQL code and not in JS code? Are you making an addon?

No, that's exactly what I didn't want to do. My interest was simply to sort my audiobooks by total running time per album.
Now I am not a very specialist in SQL (my current programming language is Julia) and I have no deep knowledge of JavaScript.
I have now created a SQL query that will accomplish the above task. If you want, you can try it out (at your own risk) in the addon: SQL Editor:

Code: Select all

SELECT
  Songs.IDAlbum, TrackType, 
  Author, Songs.Album ,
  time(SUM(SongLength) / 1000, 'unixepoch' )  AS total_time
FROM
   Songs
INNER JOIN Albums ON albums.ID = Songs.IDAlbum  AND TrackType = 2
GROUP BY
 Songs.IDAlbum
ORDER BY total_time DESC;

What else interests me is how the syntax in CustomNodes works regarding sql.(excerpt taken from the existing CustomNodes)

Code: Select all

    albums: [{
            title: 'Albums with same name',
            sql: 'ID IN (SELECT DISTINCT Albums.ID FROM Albums INNER JOIN Albums As Inline ON Albums.Album = Inline.Album AND Albums.ID <> Inline.ID)'
    }]

Can the above SQL query be used by simply inserting it into the example?

Code: Select all

  albums: [{
         title: 'Total runtime of audiobooks albums',
         sql: 'SELECT   Songs.IDAlbum, TrackType, Author, Songs.Album , time(SUM(SongLength) / 1000, 'unixepoch' ) AS total_time
	FROM   Songs
	INNER JOIN Albums ON albums.ID = Songs.IDAlbum  AND TrackType = 2
	GROUP BY  Songs.IDAlbum
	ORDER BY AS total_time ASC' 
	}]

I would be very grateful for information on this question!

Carsten

[Ludek]

Hi, watching how the sql values from customNodesDefinitions are passed to viewHanders_add.js there is code:

Code: Select all

 var defs = customNodesDefinitions.albums;
            forEach(defs, (item) => {
                var dataSource = {
                    id: item.title,
                    title: item.title,
                    description: item.description,
                    getAlbumList: function () {
                        return app.db.getAlbumList('SELECT * FROM Albums WHERE ' + item.sql, -1);
                    }
                };
                node.addChild(dataSource, 'myAlbumlistSubNodeHandler');
            });
            

=> This means that constrcuts SQL like 'SELECT * FROM Albums WHERE [[cutomNodesDefinition SQL]];

which is problematic in your case where you need to use the total_time in the ORDER BY part.

So solution is to create own node handler like

Code: Select all

nodeHandlers.myTotalRuntimeAudiobooksAlbums = inheritNodeHandler('MyTotalRuntimeAudiobooksAlbums', 'Base', {
    title: 'Total runtime of audiobooks albums',
    icon: 'album',
    hasChildren: false,
    viewAs: ['albumlist'],
    getViewDataSource: function (view) {        
        return app.db.getAlbumList("SELECT Albums.*, Songs.IDAlbum, TrackType, Author, Songs.Album , time(SUM(SongLength) / 1000, 'unixepoch' )  AS total_time FROM Songs, Albums WHERE albums.ID = Songs.IDAlbum  AND TrackType = 2 GROUP BY Songs.IDAlbum   ORDER BY total_time DESC;", -1);
    }
});

and modify nodeHandlers.myAlbumsNodeHandler to add your new myTotalRuntimeAudiobooksAlbums like this:

Code: Select all

nodeHandlers.myAlbumsNodeHandler = inheritNodeHandler('MyAlbumsNodeHandler', 'Base', {
    title: function (node) {
        return 'Albums';
    },
    icon: 'album',
    getChildren: function (node) {
        return new Promise(function (resolve, reject) {
            var defs = customNodesDefinitions.albums;
            forEach(defs, (item) => {
                var dataSource = {
                    id: item.title,
                    title: item.title,
                    description: item.description,
                    getAlbumList: function () {
                        return app.db.getAlbumList('SELECT * FROM Albums WHERE ' + item.sql, -1);
                    }
                };
                node.addChild(dataSource, 'myAlbumlistSubNodeHandler');
            });
            node.addChild(null, 'myTotalRuntimeAudiobooksAlbums');          
            resolve();
        });
    },
    viewAs: ['nodeList']
});

[sonos]

Hi Ludek,

thank you for the prompt,
As i understand i have to insert the code snippet into the viewHandlers_add.js file.

Code: Select all

nodeHandlers.myAlbumsNodeHandler = inheritNodeHandler('MyAlbumsNodeHandler', 'Base', {
    title: function (node) {
        return 'Albums';
    },
    icon: 'album',
    getChildren: function (node) {
        return new Promise(function (resolve, reject) {
            var defs = customNodesDefinitions.albums;
            forEach(defs, (item) => {
                var dataSource = {
                    id: item.title,
                    title: item.title,
                    description: item.description,
                    getAlbumList: function () {
                        return app.db.getAlbumList('SELECT * FROM Albums WHERE ' + item.sql, -1);
                    }
                };
                node.addChild(dataSource, 'myAlbumlistSubNodeHandler');
            });
            node.addChild(null, 'myTotalRuntimeAudiobooksAlbums');          
            resolve();
        });
    },
    viewAs: ['nodeList']
});

But what is with the customNodesDefinitions.js ? How do I define the sql: '... ' part next to the title line?

Carsten

[Ludek]

No need to modify customNodesDefinitions because you changed it directly in the corresponding nodeHandler.

So either modify viewHandlers_add.js as suggested above or download modified mmip from here: https://www.dropbox.com/s/pp79gh9m3wdqo ... .mmip?dl=0
including the modifications. Note that MMIP is just a ZIP file, so you can rename it to ZIP and unpack (to see the contents without installing it).

[Ludek]

BTW: Watching today's crash logs from 2684 seeing some crashes from you (like A14A29B5), it was a crash while binding track.path in the tracklist, but I don't see a reason for this on our native code. Do you have some steps to replicate or it has been caused by some code modifications in your script/addon?

[sonos]

Hi Ludeck

Have unfortunately read your post only today and can not remember how it came to this error message.

Carsten