How to show time data in standard time format HH:MM:SS?

Published:

When working with time data, it is often necessary to convert decimal values into a standard time format such as HH:MM:SS. This article provides a custom expression that can be used to achieve this conversion.

Custom Expression

The following expression can be utilized to convert decimal hours into the HH:MM:SS format:

     TO_CHAR(
     (FLOOR([column]) * 3600 +
     FLOOR(([column] - FLOOR([column])) * 60) * 60 +
     ROUND((([column] - FLOOR([column])) * 60 -
     FLOOR(([column] - FLOOR([column])) * 60)) * 60))::integer * interval '1 second',
    'HH24:MI:SS'
)

Explanation of the Expression

FLOOR([column]): This function retrieves the whole number of hours from the decimal value.
([column] - FLOOR([column])): This calculates the fractional part of the hours.
Multiplication by 60: Converts the fractional hours into minutes.
ROUND: Rounds the seconds derived from the remaining fractional minutes.
TO_CHAR: Finally, this function formats the total seconds into the desired HH:MM:SS format.

Example Output

Using the above expression, the output will display the time in the HH:MM:SS format, making it easier to read and interpret.

Conclusion

This custom expression is a useful tool for converting decimal time values into a more standard and readable format. It can be applied in various scenarios where time data needs to be presented clearly.

Additional References

Was this article useful?

Dislike

Written by Poovarasan Kandasamy

Updated

Comments (0)