PostgreSQL – Convert seconds to HH:MM:SS

PostgreSQL – Convert seconds to HH:MM:SS

You might find the need to convert the value provided in certain format in a given column to some other format.

The common example which I come across is to convert a value provided in seconds to HH:MM:SS format in a column.

So let’s take an example of a table job_summary. This table has three fields. One of the column is time_taken along with the several other columns.

The challenge in this case is to display the values in time_taken in HH:MM:SS format, instead of the seconds mentioned in there.

PostgreSQL – Convert seconds to HH:MM:SS

I will be getting some details about the jobs along with their id and the time taken.
So the following is how the current output looks like:

SELECT job_id, job_name, time_taken FROM job_summary;

Output:

 job_id | job_name | time_taken
--------+----------+------------
      1 | app_sync | 1044
      2 | db_sync  | 459
      3 | cleanup  | 278
(3 rows)

Now, let’s convert the values mentioned in the column time_taken.

The following is how we can convert the values from seconds to HH:MM:SS

SELECT job_id, job_name, TO_CHAR((time_taken || ‘ second’)::interval, ‘HH24:MI:SS’) AS time_taken FROM job_summary;

Output:

 job_id | job_name | time_taken
--------+----------+------------
      1 | app_sync | 00:17:24
      2 | db_sync  | 00:07:39
      3 | cleanup  | 00:04:38
(3 rows)

PostgreSQL – Convert seconds to HH:MM:SS

You can also convert a single integer value from second to HH:MM:SS format.

The following can be an example:

SELECT TO_CHAR((‘1044 second’)::interval, ‘HH24:MI:SS’);

Output:

 to_char  
----------
 00:17:24
(1 row)

PostgreSQL – Convert seconds to HH:MM:SS

No Comments

Post a Comment

Time limit is exhausted. Please reload CAPTCHA.