We sometimes need to convert strings to dates to intervals and back to string in Postgres. One common set of queries I reach for is to convert “seconds” field into something that is readable by human. Most of us read times in the “hh::mm::ss” format and don’t like to do math in our head.
Here’s a simple query with to_char to convert seconds into this format:
SELECT TO_CHAR('95 second'::interval, 'HH24:MI:SS') -- "00:01:35"
Assuming you have column in your table, you need to cast the field like this:
SELECT TO_CHAR((cast(call_time_seconds as varchar) ||' second')::interval, 'HH24:MI:SS') call_time_hh_mm FROM incoming_calls -- "00:21:35"
Also, don’t assume your jsonb fields will also automatically convert to
varchar the way the above query does. If you have a
jsonb field, you need to cast that as well. In this example we have a jsonb field call
SELECT TO_CHAR((cast(json_data->'call_time_seconds' as varchar) ||' second')::interval, 'HH24:MI:SS') call_time_hh_mm FROM external__incoming_calls
Here’s a step-by-step explanation of how this query works:
‘95 second’::interval: This part of the query converts the string ‘75 second’ into an interval data type. PostgreSQL allows you to cast a string to an interval if it can be parsed into a valid interval format. In this case, ‘75 second’ represents an interval of 75 seconds.
TO_CHAR(…): This is a PostgreSQL function that is used for formatting date and time values as strings. In this case, you are using it to format the interval as a string.
TO_CHAR(‘75 second’::interval, ‘HH24:MI:SS’): Here, you are passing two arguments to the TO_CHAR function:
- The first argument is the interval ‘75 second’::interval.
- The second argument is the format string ‘HH24:MI:SS’. This format string specifies how you want the interval to be formatted as a string.