DEV Community

özkan pakdil
özkan pakdil

Posted on • Originally published at ozkanpakdil.github.io on

Postgresql group by day, week and month examples

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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" 
Enter fullscreen mode Exit fullscreen mode

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" 
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up