Thursday, February 14, 2008

Postgresql group by day, week and month examples

at the end of any product there will be a reporting interfaces for 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 product in daily basis or weekly.

I used to do this in mysql like this
SELECT create_time as Date, count(id) as 'Count', FROM
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'

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.

No comments:

C# run testcontainers and run some commands on them

  using DotNet . Testcontainers . Builders ; using Xunit . Abstractions ; using IContainer = DotNet . Testcontainers . Containers . IContain...