CODEWITHSUNDEEP
postgresqltimeformatting
AndyHamp
AndyHamp

Reputation: 37

PostreSQL- How to convert float containing a number of seconds to MM:SS.sss

I have a float8 that contains a number of seconds ie 65.455. I was trying to format a column in a view so that it would read as 1:05.455.

Using postgres command like this: TO_CHAR((user_data.totaltime || ' second')::interval, 'MI:SS')

I can format it as 1:05 but i loose the precision i need.

Anyone know of a way to achieve what i need ? It doesn't look like interval allows the formatting of milliseconds.

Upvotes: 0

Views: 133

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246092

Try justify_interval:

SELECT justify_interval(
          CAST(65.455::text || ' seconds' AS interval)
       );

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

Here is a rather brute force you may do this, using the base string functions:

SELECT
    FLOOR(65.455 / 60)::text || ':' || LPAD(FLOOR(65.455 % 60)::text, 2, '0') ||
        TRIM(LEADING '0' FROM (65.455 % 1)::text);

This outputs:

1:05.455

Demo

Upvotes: 1

Related Questions