PostgresSQL:求平均停留时间--timestamp的日期相减

PostgresSQL:求平均停留时间–timestamp的日期相减

业务需求: 车辆信息表(car_info )记录了一辆车进入停车场时间(checkin_time)和离开停车场的时间(checkout_time),要求出这个停车所有车辆停留的平均时间

car_info表结构:

Tables	Are	Cool
col 3 is	right-aligned	$1600
col 2 is	centered	$12
zebra stripes	are neat	$1

id	car_num	checkin_time	checkout_time	bdid
1	沪A12346	8/20/2018 10:58:13	8/21/2018 14:58:26	04KI08
2	沪A12345	8/20/2018 10:57:58	8/21/2018 11:59:00	04KI08
3	沪A78910	8/20/2018 13:52:08	8/21/2018 14:58:32	04KI08
.	..	…	…	…

其中
“checkin_time” timestamp(0),
“checkout_time” timestamp(0),

SQL代码

--EXTRACT
SELECT
    EXTRACT (DAY FROM "avg"(P .staytime)) AS avg_day,
    EXTRACT (HOUR FROM "avg"(P .staytime)) AS avg_hour,
    EXTRACT (MINUTE FROM "avg" (P .staytime)) AS avg_minute,
    P .bdid
FROM
    (
        SELECT
            checkout_time - checkin_time AS staytime,
            bdid
        FROM
            parkingsystem.car_info
        WHERE
            bdid = '04KI08'
    ) P
GROUP BY
    P .bdid

执行结果

平均停留时长为0天1小时17分

知识点

直接用两个timestamp值相减得到一个interval值,使用EXTRACT函数获取年月日等
extract函数格式：extract （field from source）