如何在PostgreSQL中获取指定日期（当月）的API请求计数？

首先你的建表语句存在语法错误，counter字段定义后缺少逗号，正确的建表语句应为：

CREATE TABLE IF NOT EXISTS Admin (
        id SERIAL PRIMARY KEY,
        counter INTEGER NOT NULL DEFAULT 0,
        created_date TIMESTAMP NOT NULL DEFAULT Now()
);

以下分场景给出实现方案：

场景1：每条记录对应一次API请求（推荐设计） #

如果你的逻辑是每次API请求插入一条新的Admin记录（counter设为1代表单次请求），直接统计当月的记录总数即可：

SELECT COUNT(*) AS monthly_request_count
FROM Admin
WHERE created_date >= DATE_TRUNC('month', CURRENT_DATE)
  AND created_date < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';

DATE_TRUNC('month', CURRENT_DATE)会获取当月第一天的0点时间，加上1个月后就是下月第一天，能精准筛选出当月所有记录。

场景2：单条记录累加counter统计总请求数 #

如果你的逻辑是用单条Admin记录的counter字段累加所有请求次数（每次请求执行UPDATE Admin SET counter = counter +1 WHERE id = ?），原表设计无法直接统计当月请求数——因为created_date只记录记录创建时间，不会随更新同步。

这种情况下需要先修改表结构，添加记录更新时间的字段：

ALTER TABLE Admin ADD COLUMN updated_date TIMESTAMP NOT NULL DEFAULT NOW();

之后每次更新counter时同步更新updated_date：

UPDATE Admin 
SET counter = counter + 1, updated_date = NOW() 
WHERE id = 1; -- 替换为你的目标记录ID

再通过对比当月初和当前的counter值，计算当月请求数：

WITH last_month_end_counter AS (
    -- 获取上月末最后一次更新的counter值
    SELECT counter
    FROM Admin
    WHERE id = 1
      AND updated_date < DATE_TRUNC('month', CURRENT_DATE)
    ORDER BY updated_date DESC
    LIMIT 1
),
current_counter AS (
    -- 获取当前counter值
    SELECT counter
    FROM Admin
    WHERE id = 1
)
SELECT 
    COALESCE(current.counter - last_month.counter, current.counter) AS monthly_request_count
FROM current_counter current
LEFT JOIN last_month_end_counter last_month ON true;

更合理的优化方案 #

用单条记录累加请求数的设计不利于后续多维度统计（比如按天、按用户统计），更推荐单独创建请求日志表：

CREATE TABLE IF NOT EXISTS api_request_logs (
    id SERIAL PRIMARY KEY,
    admin_id INTEGER REFERENCES Admin(id), -- 关联管理员表
    request_time TIMESTAMP NOT NULL DEFAULT NOW()
);

每次API请求时插入一条日志记录：

INSERT INTO api_request_logs (admin_id) VALUES (1); -- 替换为对应管理员ID

统计当月请求数的SQL会更简洁直观：

SELECT COUNT(*) AS monthly_request_count
FROM api_request_logs
WHERE request_time >= DATE_TRUNC('month', CURRENT_DATE)
  AND request_time < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';

内容的提问来源于stack exchange，提问作者mirodil