Postgres Tricks - Convert jsonb string to hhmmss from seconds

2023-01-05

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 json_data.

  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.

Contents