Skip to content
Advertisement

Postgres convert date time difference into HH:MM:SS

Ex:

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.

Advertisement

Answer

tl;dr

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.

Duration

Calculate elapsed time as a Duration object.

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.

P5DT20H6M30S

To parse, call parse.

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.

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.

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?

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.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement