PostgreSQL Query GROUP BY Year/Month – No function matches the given name?

I’m working to write a PostgreSQL query that shows the number of new user signups create by month going back to the start of time in the database. Here’s what I have:

SELECT COUNT(id)
FROM users
WHERE users.created_at IS NOT NULL
GROUP BY YEAR(users.created_at), MONTH(users.created_at)

My goal is to have an output similar to:

2019 | August | 500
2019 | July | 444
2019 | June | 333

Problem is I’m getting the following error:

ERROR:  function year(timestamp without time zone) does not exist
LINE 4: GROUP BY YEAR(users.created_at), MONTH(users.created_at)
                 ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
, Time: 0.079000s

How can I get this query working?

Answer

In Postgres, use date_trunc():

SELECT date_trunc('month', u.created_at) as yyyymm, COUNT(*)
FROM users u
WHERE u.created_at IS NOT NULL
GROUP BY yyyymm
ORDER BY yyyymm;

Advertisement

Answer