CODEWITHSUNDEEP
postgresql
user70192
user70192

Reputation: 14234

GROUP BY day in Postgres

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!

Upvotes: 5

Views: 10775

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125544

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;

Upvotes: 10

Related Questions