How to get value from DB for specific date?-CodePudding

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.

CodePudding user response：

I would also recommend using date_part function:

SELECT COUNT(counter) 
   FROM Admin
   WHERE date_part('month', created_date) = date_part('month', current_date)
;

CodePudding user response：

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()));