at the end of any project there will be a reporting interfaces for numbers/counts. let say you build a advertisement site which gives people to publish their products on the site. they will want to see how many people visited their page in daily basis or weekly.

I used to do this in mysql like this

    SELECT create_time as Date, count(id) as 'Count', FROM
    views_of_product
    group by date_format(create_time, '%d %m %Y')
    order by date_format(create_time, '%Y-%m-%d') desc limit 7

this will nicely show last seven days views. but I needed to do same thing in postgresql. and like other days its not easily to find. I should check other report codes from project but no I allways research on google :) anyway here is my code:

    select to_char(created, 'YYYY-MM-DD') ,count(id)
    from videos_capture
    group by to_char(created, 'YYYY-MM-DD')
    order by to_char(created, 'YYYY-MM-DD') desc limit 7

its not bad actually works like a charm but understanding this code is not that easy. after I found this I started to implement needs and see better and easy to understand solutions.

weekly count example. this will show new users weekly parts for last 3 months:

    SELECT date_trunc('week', created) AS "Week" , count(id) AS "New Users"
    FROM users
    WHERE created > now() - interval '3 months'
    GROUP BY "Week"
    ORDER BY "Week" 

yearly example. this will show monthly 1 year users:

    SELECT date_trunc('month', created) AS "Month" , count(id) AS "New Users"
    FROM users
    WHERE created > now() - interval '1 year'
    GROUP BY "Month"
    ORDER BY "Month" 

I must say understanding postgresql’s sql more easy then mysql. And these sqls are from postgresql 7 :) it maynot work with the latest. Feel free to comment if they are not working.