Different output of AGE function of Retool-DB

App Building
, ,
1

I'm using the AGE( ) function of Postgres on a retool managed database to calculate the date difference between two dates.

Running the query directly on the database via sql gives following output:
image

Running the same query inside the app gives following output:
image

As you can see, all zero values are ommited.

Can anyone try to reproduce this behaviour please, i have this dates (dd.mm.yyyy):
Start: 03.12.2010
End: 03.01.2024

2

Hi @whynot,

Thanks for reaching out! I am seeing the same behavior on my side :thinking: Will try to find a workaround

1 Like
3

Would something like this work (essentially, adding in the 0 value components):

CleanShot 2024-12-10 at 14.54.40@2x
CleanShot 2024-12-10 at 14.54.40@2x888×1110 93.3 KB

    SELECT justify_interval(
        '03.01.2024 15:00:02'::timestamp - '03.12.2010'::timestamp
    ) AS justified_interval
)
SELECT jsonb_build_object(
    'years', EXTRACT(year FROM justified_interval),
    'months', EXTRACT(month FROM justified_interval),
    'days', EXTRACT(day FROM justified_interval),
    'hours', EXTRACT(hour FROM justified_interval),
    'minutes', EXTRACT(minute FROM justified_interval),
    'seconds', EXTRACT(second FROM justified_interval)
) AS interval_components
FROM interval_diff;```
4

Let me know if this solution won't work for you! Happy to take another look