John John - 2 months ago 9
SQL Question

Show only hours and higher units when subtracting timestamps (Postgres)

Normally,

age(end, start)


outputs:

X days HH:MM:SS.SSSSSSS


Which you can use
date_trunc
to round the timestamp. For example:

date_trunc('second', end - start)


outputs:

X days HH:MM:SS


Now, the problem I have is I want an output in the format of:

X days HH hours


and when I use

date_trunc('hour', processed_timestamp_utc - start_time_utc)


this outputs

X days HH:00:00


What can I do to make it output

X days HH hours


or even

X months X days HH hours MM minutes

Answer

How about this:

regexp_replace(date_trunc('hour', processed_timestamp_utc - start_time_utc)::text, ':00:00$', '');

Using regexp_replace you could also bring it to any of the other forms that you suggested.

Comments