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!