Want to calculate minutes between to columns with start_time and end_time as timestamp without zone for two customers types, and then averange the result for each.

I tried to use extract() by using the following statement, but can't get the right result:

select avg(duration_minutes) 
from ( 
  select started_at,ended_at, extract('minute' from (started_at - ended_at)) as duration_minutes 
  from my_data 
  where customer_type = 'member' 
) avg_duration;

Result:

This run sucessfuly in BQ using the following:

select avg(duration_minutes) from
(
  select started_at,ended_at,
  datetime_diff(ended_at,started_at, minute) as duration_minutes
  from my_table
  where customer_type = "member"
) avg_duration 

Result:

Wondering what might be failing in postgres?

CodePudding user response：

extract(minute from ...) extracts the field with the minutes from the interval. So if the interval is "1 hour 26 minutes and 45 seconds" the result would be 26 not 86.

To convert an interval to the equivalent number of minutes, extract the total number of seconds using extract(epoch ...) and multiply that with 60.

select avg(duration_minutes) 
from ( 
  select started_at,
         ended_at, 
         extract(epoch from (started_at - ended_at)) * 60 as duration_minutes 
  from my_data 
  where customer_type = 'member' 
) avg_duration;

Note that you can calculate the average of an interval without the need to convert it to minutes:

select avg(duration) 
from ( 
  select started_at,
         ended_at, 
         started_at - ended_at as duration
  from my_data 
  where customer_type = 'member' 
) avg_duration;

Depending on how you use the result, returning an interval might be more useful. You can also convert that to minutes using:

extract(epoch from avg(duration)) * 60 as average_minutes