another wp blog

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
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.