Reputation: 439
I have a database table which have field counter that counts number of requests to API (by updating it +1), but I want to get these counts for specific date, (for this month). Is it possible to get it? I am using PostgreSQL.
SQL query
CREATE TABLE IF NOT EXISTS Admin (
id SERIAL PRIMARY KEY,
counter INTEGER NOT NULL DEFAULT 0
created_date TIMESTAMP NOT NULL DEFAULT Now()
);
Thanks in advance.
Upvotes: 0
Views: 216
Reputation: 345
you can use subquery in two cases:
1- If with each request a field is saved in the database, then you will need the number of all fields per month:
count(counter) -> number of all fields per month.
EXTRACT(MONTH FROM created_date ) = EXTRACT(MONTH FROM Now()) -> date in this month
Query :
select count(counter) as "counter In This month"
from Admin
where created_date in( select created_date from Admin where EXTRACT(MONTH FROM created_date ) = EXTRACT(MONTH FROM Now()));
2- If you update the counter after each request, so that the counter in one day equals the number of all requests on the same day.
sum(counter) -> Total number of all requests per month
EXTRACT(MONTH FROM created_date ) = EXTRACT(MONTH FROM Now()) -> date in this month.
Query :
select sum(counter) as "counter In This month"
from Admin
where created_date in( select created_date from Admin where EXTRACT(MONTH FROM created_date ) = EXTRACT(MONTH FROM Now()));
Upvotes: 1
Reputation: 336
I would also recommend using date_part
function:
SELECT COUNT(counter)
FROM Admin
WHERE date_part('month', created_date) = date_part('month', current_date)
;
Upvotes: 0