如何使用带有JSONB值的日期/时间函数？

Question

我通常使用以下语法按天分组：

SELECT date_trunc('day', loggedin) AS "Day" , count(*) AS "No. of users"
FROM logins
WHERE created > now() - interval '3 months' 
GROUP BY 1 
ORDER BY 1;

现在，我的日期值位于名为额外内容的JSONB列中。

SELECT date_trunc('day', "extras"->>'sent') AS date , count(*) AS "value"
FROM "document"
GROUP BY 1 
ORDER BY 1;

这会产生以下错误：

错误：函数日期（未知，文本）不存在第1行：选择日期（'day'，“extras”-

有没有办法让这一切顺利进行？

Answer 1

将的文本转换为exsu-

SELECT date_trunc('day', ("extras"->>'sent')::date) AS date , count(*) AS "value"
FROM "document"
GROUP BY 1 
ORDER BY 1;