Question 114903 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to get value from DB for specific date?

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.

Tags:

2

Answers


  1. 0

    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()));
    Login or Signup to reply.
  2. 0

    I would also recommend using date_part function:

    SELECT COUNT(counter) 
   FROM Admin
   WHERE date_part('month', created_date) = date_part('month', current_date)
;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search