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