Format postgres interval type

[berik]

Hi all,

PostgreSQL supports timestamps, for example they can be returned when comparing dates:

select now() - creation_date from my_table;

Retool decodes the results to a JSON object, for example:

I was looking for a formatting option for PostgreSQL timestamps, but could not find anything available.
Thus I created this library function, which can be dropped in the App Settings > Preloaded JS:

window.formatPGInterval = function(interval) {
    if ('years' in interval && interval.years != 0) {
        return interval.years + " years"
    } else if ('months' in interval && interval.months != 0) {
        return interval.months + " months"
    } else if ('weeks' in interval && interval.weeks != 0) {
        return interval.weeks + " weeks"
    } else if ('days' in interval && interval.days != 0) {
        return interval.days + " days"
    } else if ('hours' in interval && interval.hours != 0) {
        const roundup = (interval.minutes || 0) > 30 ? 1 : 0
        return (interval.hours + roundup) + " hours"
    } else if ('minutes' in interval && interval.minutes != 0) {
        const roundup = (interval.seconds || 0) > 30 ? 1 : 0
        return (interval.minutes + roundup) + " minutes"
    } else if ('seconds' in interval && interval.seconds != 0) {
        const roundup = (interval.milliseconds || 0) > 500 ? 1 : 0
        return (interval.seconds + roundup) + " seconds"
    } else {
        return (interval.milliseconds || 0) + " milliseconds"
    }
}

And used in a value mapper like {{formatPGInterval(item)}}

Which gives a more clean description of the interval:

I'm offering this to share with the community. It'd also be nice if there would be some build-in formatter / component to represent intervals in retool.

Have a great day!
Berik

[ScottR]

Why not use moment() ?

[berik]

Hi ScottR,

Turns out the moment library works great. Just had to figure out how to use it.
I ended up using {{moment.duration(item).humanize()}} to format the pg interval values.

Thanks for the pointer!

Cheers,
Berik

[berik]

Using moment().duration(item).humanize() renders a string, that will not be sorted correctly.
It would be a great addition if Retool supports a "Timeinterval" data type for tables, so that formatting and sorting works out of the box!

[alex-w]

To add onto the Moment direction: one common option here in tables is to uncheck the "Sort by mapped values" setting on the column, to instead sort the column by the underlying value in the dataset.

That way you can use moment in the mapper of the column to format the value to display however you'd like (such as what you ended up doing with {{moment.duration(item).humanize()}}!) without it changing the sort order that the end user sees.

[berik]

Hi Alex,

That looks very promising!
I can't seem to find this option. When I open a column configuration, retool shows:

I don't see how to open the popup window. Could this be a retool version difference? Or is it because my table uses a resource query instead of hardcoded data?

Thanks for the suggestion!
-Berik