主页 / dba / 问题 / 345521
Accepted
muttBunch
Asked: 2025-03-02 05:45:07 +0800 CST

如何在 Postgres 中检索基于年初至今的数据？

  • 772

我想在以下查询中检索“年初至今”的数据。我希望在“INTERVAL”中找到一些简单的内容，但找不到任何东西。

我有以下内容：

SELECT
    "status_id", date_part('epoch', time) * 1000 as time,
    COUNT(time) as "count"
FROM evStatus
WHERE time >= NOW() - Interval '{1}'
GROUP BY "status_id", time

我想要获取从每年一月到当前日期的数据。

我在下面进行了测试并且它似乎有效，但是想确保这是正确的方法吗？

SELECT
    "status_id", date_part('epoch', time) * 1000 as time,
    COUNT(time) as "count"
WHERE time BETWEEN date_trunc('year', now()) AND CURRENT_TIMESTAMP
postgresql

2 个回答

  1. Best Answer

    这个看似简单的查询所包含的内容比你想象的要多。

    假设该列是类型timestamp with time zone（而不是time或timestamp without time zone） - 否则会出现更多问题 - 我继续使用列名"tstz"。“时间”会产生误导，并且也是标准 SQL 中的保留字。

    SELECT status_id
     , extract(epoch FROM tstz) * 1000 AS tstz_epoch  -- ①  
     , count(*) AS row_count                          -- ②
FROM   evStatus                                       -- ③
WHERE  tstz >= date_trunc('year', now(), 'UTC')       -- ④
-- AND tstz <= now()                                  -- ⑤
GROUP  BY status_id, tstz;

    ① 最好不要将输入名称（原文中的“时间”）重复为输出名称。这可能会造成混淆。您稍后的引用GROUP BY将重定向到输出列名称（隐藏相同的输入名称），但根据输入会便宜得多。请参阅：

    ② 之后的WHERE tstz >= <anything>结果集不能包含tstz IS NULL，所以count(*)100%等效，并且比更简单、更快count(tstz)。

    ③ 奇怪的是，您的原始查询引用了"status_id"，其中引号只是噪音，但没有evStatus，它实际上被视为evstatus（全部小写）。不确定这是否是故意的。请参阅：

    ④ date_tunc(field, timestamptz)（没有时区作为第三个参数）不是不可变的表达式。它默认使用timezone执行会话的设置来确定一年的确切开始时间。几个小时通常无关紧要。但它可能会引入极端错误，如果您不了解的话，很难确定。你可能真的想要date_tunc(field, timestamptz, time_zone)。我在示例中使用了“UTC”。替换为您的时区名称。相关：

    ⑤ 通常，设计上不允许有未来条目。那么你可以直接使用WHERE tstz >= date_trunc('year', now())。更简单、更快捷。（否则包括第二个过滤器。）

    Postgres 中时间戳类型的基础知识：

    • 4 
  2. WHERE "time" >= NOW() - INTERVAL '1 YEAR'

    不要使用诸如"time"表名或列名之类的关键字！

    • 3

相关问题