GROUP BY day in Postgres

Question

I'm new to Postgres. I have a table that looks like this:

User
----
id (int)
createdOn (bigint)
isDeleted (boolean)

The createdOn represents the number of milliseconds since EPOCH. I'm trying to figure out how many users were created on each day that have an isDeleted flag of 0. I've tried the following, but it doesn't work:

SELECT date_trunc('day', u.createdOn) AS "Day" , count(*) AS "No. of users"
FROM User u
WHERE u.isDeleted = 0 
GROUP BY 1 
ORDER BY 1;

How do I create this query?

Thanks!

Answer 1

You do not say what does not work but I guess it is the date_trunc function

select
    date_trunc('day', to_timestamp(createdOn)) as "Day",
    count(*) as "No. of users"
from User
where isDeleted = 0
group by 1
order by 1;