CODEWITHSUNDEEP
databasepostgresql
Pawan Sharma
Pawan Sharma

Reputation: 319

PostgreSQL Monthly Database growth

I have table in which i am calculating daily database growth.

 time_stmp| datname| datsize
----------------------------+----
 2018-05-1  | test    | 35 MB   
 2018-05-2  | test    | 38 MB    
 2018-05-3  | test    | 49 MB
.    
.    
.    
.    
 2018-05-29  | test    | 57 MB    
 2018-05-31  | test    | 62 MB

Even, i am able to calculate the total growth at the end of the month using below query

select
    time_stmp,datname,pg_size_pretty(sum(datsize))
from
    testtable
where
    to_char(time_stmp, 'YYYY-MM-DD') = to_char(date_trunc('month', CURRENT_DATE) + interval '1 month - 1 day','YYYY-MM-DD')
group by 1,2;


     time_stmp| datname| datsize
----------------------------+----
 2018-05-31  | test    | 62 MB

But i am not able to find exact total growth in each month and generate a single table output like below 

 time_stmp| datname| datsize
----------------------------+----
2018-01-1 | test | 35 MB
2018-02-1 | test | 50 MB
2018-03-1 | test | 75 MB
.    
.    
.
2018-12-1 | test | 300 MB

Thanks for your response..

Upvotes: 2

Views: 3741

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521864

Try this query:

SELECT
    time_stmp, datname, pg_size_pretty(datsize)
FROM
    testtable
WHERE
    to_char(time_stmp + interval '1 day', 'YYYY-MM-DD') =
    to_char(date_trunc('month', time_stmp) + interval '1 month', 'YYYY-MM-DD');

Here is a short demo which shows that the timestamp logic is correct, and that the ends of months are correctly identified:

Demo

Upvotes: 1

Related Questions