如何编写PostgreSQL查询获取近30天按天截断的Unix时间戳数据？

你的trade_time是毫秒级Unix时间戳（示例中为13位数字），需要先转换成PostgreSQL可识别的时间类型，再按天截断分组。以下是完整实现方案：

核心查询语句 #

SELECT
    -- 将毫秒时间戳转成日期并截断到天
    DATE_TRUNC('day', TO_TIMESTAMP(trade_time::BIGINT / 1000)) AS trade_date,
    -- 统计每日交易总笔数
    COUNT(*) AS total_trades,
    -- 统计每日总交易额
    SUM(trade_value) AS total_trade_value,
    -- 统计每日平均交易价格
    AVG(price_in_quote_asset) AS avg_trade_price,
    -- 统计买方做市商交易占比（保留两位小数）
    ROUND(COUNT(CASE WHEN is_buyer_maker THEN 1 END)::NUMERIC / COUNT(*) * 100, 2) AS maker_trade_percentage
FROM
    your_trade_table -- 替换为你的实际表名
WHERE
    -- 筛选过去30天的交易数据（包含当日）
    TO_TIMESTAMP(trade_time::BIGINT / 1000) >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY
    trade_date
ORDER BY
    trade_date DESC;

关键逻辑说明 #

• 时间戳转换：TO_TIMESTAMP(trade_time::BIGINT / 1000)将毫秒级时间戳转为PostgreSQL的timestamp类型（TO_TIMESTAMP默认接收秒级时间戳，因此需除以1000）。
• 按天截断：DATE_TRUNC('day', ...)会把时间戳统一截断到当日0点，确保同一天的交易被归为同一组。
• 时间范围筛选：CURRENT_DATE - INTERVAL '30 days'精准定位30天前的日期，若需排除当日未完成的交易，可替换为NOW() - INTERVAL '30 days'。

性能优化建议 #

• 若数据量较大，给trade_time字段建立索引可加速时间范围查询：

  CREATE INDEX idx_trade_time ON your_trade_table (trade_time);
  

• 涉及时区需求时，可在转换时指定时区，例如：

  DATE_TRUNC('day', TO_TIMESTAMP(trade_time::BIGINT / 1000) AT TIME ZONE 'Asia/Shanghai') AS trade_date
  

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