Convert unix timestamp to a date format, group by it and count

Question

I've found a post describing how to count records grouped by date format. However, the author in this post uses MySQL:

select count(*), DATE_FORMAT(created_at,"%Y-%m-%d") as created_day FROM widgets GROUP BY created_day

Also, I store a unix timestamp under the started_at field, so first I would have to convert it to a date or timestamp format. How can I do all of this in PostgreSQL?

Answer 1

Try something like:

select count(*), date_trunc(to_timestamp(created_at),'day') as created_day 
FROM widgets 
GROUP BY created_day;

to_timestamp converts unix timestamp to a proper timestamp and date_trunc truncates the timestamp to days.

Answer 2

Ok, I figured it out thanks to your comments! I just had to do something like this with the timestamp:

to_char(to_timestamp(started_at), 'YYYY-Mon')