Working with time intervals in PostgreSQL and Excel

By default, PostgreSQL (PSQL to its friends) formats time intervals in a way that makes it much more human-readable, which is nice until you have intervals longer than a day and you want to manipulate the data further in something like Microsoft Excel.

To make the time intervals machine-readable again, you can use something like the following (which I found here):

SELECT date_part('epoch', time_interval_field) * INTERVAL '1 second';

This extracts the number of seconds from your interval, and then multiplies it by an interval in order to convert it back to the INTERVAL type. Seems silly, but this forces the formatting into hh:mm:ss, so if you have more than 24 hours, rather than seeing 1 day 12:00:35.12312, you’ll get 36:00:35.12312.

Then all that remains is to use a custom format in Excel when loading your data:

[h]:mm:ss

The square brackets tell Excel to allow values of more than 24 hours (rather than calculating hours % 24 as it would without), and the rest is fairly self-explanatory. If you care about milliseconds, you can include them with [h]:mm:ss.00000.