Why doesn't postgres reduce a timespan fully when printing?

Question

I've got a table that looks something like this:

create table room_server_metrics (
  namespace  varchar(36) not null,
  session_id varchar(36) not null,
  primary key (namespace, session_id),

  created_at timestamptz   not null default now(),
  updated_at timestamptz   not null default now(),
);

When I try to sum the length of all sessions, I get this:

database=> select sum(updated_at - created_at) as total_time from room_server_metrics;
         total_time         
----------------------------
 94 days 60951:01:56.381483

94 days + 60951 hours is ~2633 days.

I wasn't sure if the print out was showing 94 days as an easier metric to read, but if I print out the delta in seconds, I get this:

database=> select extract(epoch from sum(updated_at - created_at)) as total_time from room_server_metrics;
         total_time         
----------------------------
 227546617.181704

227546617.181704 seconds is also ~2633 days.

Why does the first query not reduce the number of hours past 94 days? I can't for the life of me figure this one out haha.

Max

Answer 1

Thanks to Adrian's comment and some postgres docs research I found this:

Internally interval values are stored as months, days, and seconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a daylight savings time adjustment is involved. The months and days fields are integers while the seconds field can store fractions. Because intervals are usually created from constant strings or timestamp subtraction, this storage method works well in most cases. Functions justify_days and justify_hours are available for adjusting days and hours that overflow their normal ranges.

It seems postgres takes each row's timespan and adds up the days/hours/minutes separately. In this case, there were 94 rows with at least 1 day worth of time in the interval that was summed up. Using the justify_hours function does indeed solve the problem:

database=> select justify_hours(sum(updated_at - created_at)) as total_time from room_server_metrics;
        total_time         
---------------------------
 2637 days 08:33:22.773599

Answer 2

extract(epoch from ...) is going to give garbage data. Epoch timestamps are only meaningful for actual timestamps, not for interval types.

Try justify_days:

select justify_days(sum(updated_at - created_at)) as total_time from room_server_metrics;

               justify_days
-------------------------------------------
 11 years 10 mons 2 days 2130:18:50.504887

The PostgreSQL manual has this to say:

Internally interval values are stored as months, days, and microseconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a daylight savings time adjustment is involved. The months and days fields are integers while the microseconds field can store fractional seconds. Because intervals are usually created from constant strings or timestamp subtraction, this storage method works well in most cases, but can cause unexpected results:

SELECT EXTRACT(hours from '80 minutes'::interval);
date_part
-----------
        1

SELECT EXTRACT(days from '80 hours'::interval);
date_part
-----------
        0

Functions justify_days and justify_hours are available for adjusting days and hours that overflow their normal ranges.

Answer 3

To be able to achieve the result you want you can use the justify_interval function.The following function consolidates the days, hours, and other components of an interval, providing a more human-readable format. Here's how you can use it in your query:

database => select justify_interval(sum(updated_at - created_at)) as total_time
from room_server_metrics;