如何在PostgreSQL中计算用户首次与末次会话的天数（无datediff）

需求 #

从user_sessions表中查询2020年每位用户首次会话与末次会话之间的间隔天数。

MySQL可行实现 #

在MySQL中可直接用datediff函数实现，代码如下：

select user_id,
datediff(max(date(created_at)),min(date(created_at))) as noofdays
from user_sessions
where Year(created_at)=2020
group by 1
order by 1;

PostgreSQL错误尝试及问题分析 #

尝试用Extract(day from age(...))的语句得到的结果远小于预期，代码如下：

select user_id,
Extract(day from age(max(created_at),min(created_at))) as noofdays
from user_sessions
Where Extract(YEAR FROM created_at)=2020
group by user_id
order by user_id

问题原因：age()函数返回的是包含年、月、日的时间间隔（比如1 year 10 mons 18 days），Extract(day)只会提取间隔中的“日”部分，不会将年和月转换为天数，因此结果仅显示间隔中的剩余天数，而非总天数。

PostgreSQL正确解决方案 #

方法一：日期差直接转整数 #

PostgreSQL中两个日期/时间类型相减会得到interval类型，将其直接转换为整数即可得到总天数：

select user_id,
       (max(created_at) - min(created_at))::int as noofdays
from user_sessions
where extract(year from created_at) = 2020
group by user_id
order by user_id;

方法二：通过时间戳秒数计算 #

将时间间隔转换为epoch秒数，除以一天的秒数（86400）得到总天数：

select user_id,
       floor(date_part('epoch', max(created_at) - min(created_at)) / 86400)::int as noofdays
from user_sessions
where extract(year from created_at) = 2020
group by user_id
order by user_id;

这两种方法都能得到正确的总天数间隔，与预期结果一致。

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