postgresql - 如何使用具有 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;

现在我的日期值位于名为 extras 的 JSONB 列中。

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

这给出了这个错误:

ERROR: function date_trunc(unknown, text) does not exist LINE 1: SELECT date_trunc('day', "extras"->>'sent') AS date , count(... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

有没有办法让它工作？

Answer 1

将 extras->>'sent' 的文本转换为 date:

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