Sum time with format HH:MM with javascript

[Sprinter441]

This is the thing: I’m trying to get a formula to sum the time of the cells of a column (the column is a “DB folder” plugin column). The formula can be entered in the footer of the database (the database is a note created as “DB folder” that retrieves the metadata from the notes in certain folder and shows them in columns) and it has to be in javascript.

Basically the formula has to sum the values in the column, the format is HH:MM (text type).

For example if one cell has “2:00” and the other cell has “3:15” the result shown should be “5:15”.

The place to enter the formula is opened once I click on the footer

What I’m trying to do

Creating a javascript formula that sums all the time in format HH:MM (the cells are formatted as text).

In the “Db folder” documentation it says:

Footer Formula
In case you are using a footer formula, you can also use the following variables:

• values : An Array of cell values in the column

Having these in consideration, I started to think with logic how to proceed (since I don’t know at the moment know how to code).

Things I have tried

I’ve been searching for hours online to get a javascript formula that sum time in HH:MM format, and it works here. I’ve been doing some work with chat GPT also, I don’t know nothing about javascript, but I’m using logic to get the answer.

This is the best formula I’ve created so far by prompting the issue to chatGPT. The problem is that it doesn’t return any value.

ChatGPT says it’s correct, after trying with different promts. But still no success in showing the sum of the values after putting these code in the footer. Any idea about how to sum these HH:MM values?, or any suggestion about correcting the code?

    let totalSeconds = values.reduce((acc, val) => {
        const [hoursPart, minutesPart] = val.split(':');
        const hours = parseInt(hoursPart.trim().padStart(2, '0'));
        const minutes = parseInt(minutesPart.trim().padStart(2, '0'));
        return acc + hours * 3600 + minutes * 60;
    }, 0);

    const hours = Math.floor(totalSeconds / 3600);
    const minutes = Math.floor((totalSeconds % 3600) / 60);

    return `${String(hours).padStart(2, '0')}:${String(minutes).padStart(2, '0')}`;
}

Best regards

[holroy]

There seems to be an extra brace, }, at the end. Is that there in your code, or is it just a copy paste error?

The code does some stupid stuff, which is to be expected from ChatGPT, but it seems to be kind of correct and should otherwise produce a result.

There is in your case no need to transform it all to seconds, as you could easily just work in minutes, and there is no need to reformat the string with leading zeros before doing the parseInt().

[holroy]

For a formula to work in DB folder it seems you use a template literal expression, like ${ yourVariable }. And the expression used instead of yourVariable needs to conform to that syntax which is legal inside such an expression. This leaves you with two options, either to have a monster expression, or to use functions.

Using monster one-line expressions

The following seems to do the trick (all at one line):

${ values.reduce((acc, val) => { const t = val.split(':'); acc[0] = acc[0] + parseInt(t[0]) * 60 + parseInt(t[1]); acc[1] = String(Math.floor(acc[0] / 60)).padStart(2, '0') + ":" +  String(Math.floor(acc[0] % 60)).padStart(2, '0'); return acc }, [0, ''])[1] }

Here is the same version spread out across multiple lines (which doesn’t work as a formula inside DB folder field):

${ values.reduce((acc, val) => {
 const t = val.split(':');
 acc[0] = acc[0] 
  + parseInt(t[0]) * 60
  + parseInt(t[1])
acc[1] = 
  String(Math.floor(acc[0] / 60))
    .padStart(2, '0') 
  + ":" +
  String(Math.floor(acc[0] % 60))
    .padStart(2, '0') 
return acc
}, [0, ''])[1] }

The trick I’m utilising here is to actually keep using two accumulated version during the reduce(). One for the minutes total so far, and one with the formatted result so far during the reduction. This means that for each loop of the reduction we calculate the result. Then when the reduction ends, we picks out just the second element (aka the result) using [1] .

An alternative version of the same using IIFE’s looks like this:

${ ((min) => String(Math.floor(min / 60)).padStart(2, '0') + ':' + String(Math.floor(min % 60)).padStart(2, '0'))( values.reduce((acc, val) => { const t = val.split(':'); return acc + parseInt(t[0]) * 60 + parseInt(t[1])}, 0)) }

The same on multiple lines:

${ 
  ((min) => String(Math.floor(min / 60)).padStart(2, '0')
    + ':' + String(Math.floor(min % 60)).padStart(2, '0'))
  ( values.reduce((acc, val) => {
      const t = val.split(':');
      return acc 
        + parseInt(t[0]) * 60 
        + parseInt(t[1])
    }, 0) ) 
}

This is even more convoluted if you’re not used to IIFE’s. Here the two first lines declare a function with one parameter, min, and format the minutes to a “HH:mm” format, and the last lines is actually the function invocation setting that min parameter to be the values.reduce( ... ) result. The reduction is the same as before, but now we’re back to just using that one accumulated value.

In any case, using these one-liners you need to be very careful to include semicolons, keep it to actually be one line, and not to use ". All of this because it seems DB Folder doesn’t sanitise the result of your input into the formula field.

Using functions

A better, clearer version for formulas is to use functions, but this requires you to create a separate .js file, like sumTime.js which is in a folder scanned by the DB folder plugin. Contents of that javascript file could then be:

/**
 * Sums column values of the form "HH🔨
 * 
 * Insert this in formula field: ${db.js.sumTime(values)}
 */
function sumTime(colValues) {
  let min = colValues.reduce((acc, val) => {
      const t = val.split(':');
      return acc + parseInt(t[0]) * 60 + parseInt(t[1])
    }, 0);
 
  return  String(Math.floor(min / 60)).padStart(2, '0') +
    ':' + String(Math.floor(min % 60)).padStart(2, '0')
}

module.exports = sumTime

And as stated in the comments, you’d then need to use ${ db.js.sumTime(values) } in the formula field.

I reckon it goes without saying that this is a much cleaner version, and easier to work with, given that you’ve got a way to edit the javascript file.

[Sprinter441]

I want to give you a big hug man, your formula works like charm. I’ve been doing a lot of research trying to find the answer about how to sum time correctly, and finally, you shared me the solution and more. Thank you for so much value.

I’m learning some new stuff with your response, specially about using functions with DB folder.

What I’m trying to do next, is to get the average of the values along with the sum of the total. With text like "Total: ", "Average: ". Is that possible within the same formula?

I’ve seen that the creator of DB folder shared a footer formula in his github to get the average value of the columns: obsidian-db-folder/docs/examples/footerFormulas/obtainAverage.js at f7038d1fe8f6e4237a7cd2e3f82a040826c73f07 · RafaelGB/obsidian-db-folder · GitHub

I’m experimenting with AI and the codes to get a solution but I got weird results so far, none of them correct atm. Is it possible to do so?

[Sprinter441]

Finally, I’m getting the correct result. My first javascript code without knowing javascript that seems to work (at least for the sample I’m testing). After trying to understand a bit of the logic behind the code you’ve made, and test it out with ChatGPT, this one seems to do the trick:

${(() => {
    const totalMinutes = values.reduce((acc, val) => {
        const [hours, minutes] = val.split(':').map(Number);
        return acc + hours * 60 + minutes;
    }, 0);
    
    const totalHours = Math.floor(totalMinutes / 60);
    const totalMinutesRemainder = totalMinutes % 60;
    const total = `${String(totalHours).padStart(2, '0')}:${String(totalMinutesRemainder).padStart(2, '0')}`;

    const averageTotalMinutes = Math.floor(totalMinutes / values.length);
    const averageHours = Math.floor(averageTotalMinutes / 60);
    const averageMinutes = averageTotalMinutes % 60;
    const average = `${String(averageHours).padStart(2, '0')}:${String(averageMinutes).padStart(2, '0')}`;

    return `Total: ${total}, Promedio: ${average}`;
})()}

Using a monster one-line expression (the one I tested in the footer):

${(() => {const totalMinutes = values.reduce((acc, val) => {const [hours, minutes] = val.split(':').map(Number);return acc + hours * 60 + minutes;}, 0);const totalHours = Math.floor(totalMinutes / 60);const totalMinutesRemainder = totalMinutes % 60;const total = `${String(totalHours).padStart(2, '0')}:${String(totalMinutesRemainder).padStart(2, '0')}`;const averageTotalMinutes = Math.floor(totalMinutes / values.length);const averageHours = Math.floor(averageTotalMinutes / 60);const averageMinutes = averageTotalMinutes % 60;const average = `${String(averageHours).padStart(2, '0')}:${String(averageMinutes).padStart(2, '0')}`;return `Total: ${total}, Promedio: ${average}`;})()}

Result so far:

If you can spot something wrong in this last code that could lead to weird stuff I’ll appreciate the correction