Postgres convert date time difference into HH:MM:SS

Ex:

startTime-EndTime 

Diff is 0 years 0 months 5 days 20 hours 6 minutes 30 seconds. I want to convert this into HH:MM:SS format : 120:06:30.

Answer

tl;dr

Duration                                                 // Represent a span-of-time in terms of total number of whole seconds plus a fractional second in nanos.
.between(                                                // Calculate elapsed time.
    myResultSet.getObject( … , OffsetDateTime.class ) ,  // Start
    myResultSet.getObject( … , OffsetDateTime.class ) ,  // Stop
)                                                        // Returns a `Duration` object.
.toString()                                              // Generate text in standard ISO 8601 format of `PnYnMnDTnHnMnS`. 

java.time

Doing it on the Java side is simple with a column of a data type akin to the SQL-standard TIMESTAMP WITH TIME ZONE and driver compliant with JDBC 4.2 or later (for support of the modern java.time classes).

OffsetDateTime

Retrieve your moments as OffsetDateTime objects, per JDBC 4.2 spec.

OffsetDateTime start = myResultSet.getObject( … , OffsetDateTime.class ) ;
OffsetDateTime stop = myResultSet.getObject( … , OffsetDateTime.class ) ;

Duration

Calculate elapsed time as a Duration object.

Duration d = Duration.between( start , stop ) ;

ISO 8601

Generate a standard ISO 8601 string of the format PnYnMnDTnHnMnS where the P marks the beginning (probably stands for “period” – no standardized terms in date-time handling unfortunately), and the T separates years-months-days from hours-minutes-seconds. So an hour and a half would be PT1H30M. Your example of 5 days 20hours 6minutes 30 seconds would be P5DT20H6M30S.

The java.time classes use the ISO 8601 format by default. So you generate text by simply calling toString. No need to specify a formatting pattern.

String output = d.toString() ;

P5DT20H6M30S

To parse, call parse.

Duration d = Duration.parse( "P5DT20H6M30S" ) ;

Note that Duration counts days as 24-hour chunks of time, without regard for the calendar. If you want calendar-based dates, use Period class. If you want both concepts together, use PeriodDuration class from the ThreeTen-Extra, but think twice as mixing the two concepts is usually unwise and impractical.

I strongly advise you not represent a span-of-time using the clock notation as shown in your Question. Doing so is ambiguous, error-prone with people misinterpreting the text, as I have personally seen happen in business scenarios. The standard format is much wiser.

Duration::to…Part

But if you insist on the clock-formatting, create a string by calling the to…Part methods on Duration.

String output = d.toDaysPart() + ":" + d.toHoursPart() + ":" + d.toMinutesPart() + ":" + d.toSecondsPart() + "." + d.toNanosPart() ; 

toHours versus toHoursPart

If want all the days reported as hours, get total number of hours across entire span-of-time by calling toHours rather than toHoursPart. Then get the parts of minutes and seconds.

Duration d = Duration.between( start , stop ) ;
String output = d.toHours() + ":" + d.toMinutesPart() + ":" + d.toSecondsPart() ;

120:06:30

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, Java SE 10, Java SE 11, and later – Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Most of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.