Database Queries - ashishranjandev/developer-wiki GitHub Wiki

Arrange your count based on dates

SELECT date(updated_at), count(updated_at) as total_count 
FROM "persons"
WHERE ("persons"."updated_at" BETWEEN '2012-10-17 00:00:00.000000' AND '2012-11-07     12:25:04.082224') 
GROUP BY date(updated_at)
ORDER BY count(updated_at) DESC -- this line changed!

Group by weekly

SELECT 'Week Starting from ' || substring(subz.weekly from 1 for 10) as week_name, subz.weekly_count from (
SELECT to_char(date_trunc('week', created_date_time::date), 'YYYY/MM/DD')  AS weekly,
       COUNT(1) as weekly_count          
FROM table_name where col_name = 'Successful update of password' 
GROUP BY weekly
ORDER BY weekly) as subz;

CAST Date

TO_CHAR(timestamp_column, 'DD/MM/YYYY') as submission_date

Group by Hour

SELECT date_trunc('hour', completed_timestamp), count(1) from tasks where completed_timestamp > CURRENT_DATE - 1 
group by date_trunc('hour', completed_timestamp);