如何将平均时间值转为整数并将时长向上取整为天数（如72天32h转73）

需要将SQL查询结果中的平均时间值转换为整数，且包含小时的时长需向上取整为天数（例如72 days 32:01:52.694268转换为73）。

原始查询SQL #

select day_of_week, avg(booking_time)
from(SELECT check_in_date - booked_at as booking_time, EXTRACT (dow FROM 
    check_in_date)day_of_week
    FROM bookings 
    ) as table1
where EXTRACT(epoch FROM booking_time)/3600 > 0
group by day_of_week
order by avg(booking_time) desc;

原始查询输出 #

解决方案 #

通过EXTRACT(epoch ...)将平均时间间隔转换为总秒数，再除以一天的秒数（3600*24）得到带小数的天数，最后用ceil()函数向上取整即可实现需求。修改后的SQL如下：

select 
    day_of_week,
    ceil(EXTRACT(epoch FROM avg(booking_time)) / (3600 * 24)) as avg_booking_days
from(
    SELECT 
        check_in_date - booked_at as booking_time, 
        EXTRACT(dow FROM check_in_date) as day_of_week
    FROM bookings 
) as table1
where EXTRACT(epoch FROM booking_time)/3600 > 0
group by day_of_week
order by avg_booking_days desc;

处理后预期输出 #

内容的提问来源于stack exchange，提问作者vikwillberg