Search code examples
postgresqljsonb

How to use Date/Time functions with JSONB values?

I am usually using this syntax to group by day:

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;

Now my Date-value is inside a JSONB column called extras.

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

This gives this error:

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.

Is there a way to make this work?

Solution

  • Cast the text of extras->>'sent' to date:

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