Convert SongLength into h:min:sec
Moderators: jiri, drakinite, Addon Administrators
Convert SongLength into h:min:sec
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
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
Re: Convert SongLength into h:min:sec
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?
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?
Re: Convert SongLength into h:min:sec
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
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
Student electrical-computer engineer, web programmer, part-time MediaMonkey developer, full-time MediaMonkey enthusiast
I uploaded many addons to MM's addon page, but not all of those were created by me. "By drakinite, Submitted by drakinite" means I made it on my own time. "By Ventis Media, Inc., Submitted by drakinite" means it may have been made by me or another MediaMonkey developer, so instead of crediting/thanking me, please thank the team. You can still ask me for support on any of our addons.
Re: Convert SongLength into h:min:sec
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.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?
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;
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)'
}]
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'
}]
Carsten
Re: Convert SongLength into h:min:sec
Hi, watching how the sql values from customNodesDefinitions are passed to viewHanders_add.js there is code:
=> 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
and modify nodeHandlers.myAlbumsNodeHandler to add your new myTotalRuntimeAudiobooksAlbums like this:
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');
});
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);
}
});
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']
});
Re: Convert SongLength into h:min:sec
Hi Ludek,
thank you for the prompt,
As i understand i have to insert the code snippet into the viewHandlers_add.js file.
But what is with the customNodesDefinitions.js ? How do I define the sql: '... ' part next to the title line?
Carsten
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']
});
Carsten
Re: Convert SongLength into h:min:sec
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).
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).
Re: Convert SongLength into h:min:sec
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?
Re: Convert SongLength into h:min:sec
Hi Ludeck
Have unfortunately read your post only today and can not remember how it came to this error message.
Carsten
Have unfortunately read your post only today and can not remember how it came to this error message.
Carsten