从now()函数中减去小时数

Question

我们有一台机器24小时运行。每天我要报告它每小时生产的产品数量。在我们这里，一个工作日指的是例如从 '2015-06-16 06:00:00' 到 '2015-06-17 06:00:00'。

这是我的代码:

select date_trunc('hour', t_el_eventlog.eventtime at time zone 'CET') as hours,
       count (distinct t_el_eventlog.serialnumber) as count
from t_el_eventlog
where eventtime at time zone 'CET' between '2015-06-16 06:00:00'
                                       and '2015-06-17 06:00:00'
and sourceid = '44'
group by hours
order by hours asc

• 我的Postgres版本：“PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 32-bit”

• 我正在处理的两个列的数据类型：

eventtime timestamp without time zone
sourceid  integer NOT NULL

• 时区为"Europe/Berlin"。

通过上述查询，我获得了所需的信息，但每天都必须更改日期。 是否可以将now()函数用作我的情况的默认值，以便我不必每天手动更改日期？

- Chanti

---

我想知道前一天的输出...类似于“2015-06-16 06:00:00”和“2015-06-17 06:00:00”。 - Chanti

'2015-06-16 06:00:00'和'2015-06-17 06:00:00'是一个工作日，我想知道在2015年6月17日上午9点/10点的上述工作日的输出。 - Chanti

像这样的问题，您需要提供您的确切表定义（以及始终如一的Postgres版本）。确切的数据类型很重要。此外，请告诉我们更多关于您的时区的信息。“at time zone 'CET'”很可能不是您想要的。 - Erwin Brandstetter

@ErwinBrandstetter：最初我也使用了上述代码而没有时区，但后来我的一个IT背景的朋友告诉我要使用CET时区（我想这是因为夏令时设置）。 - Chanti

@ErwinBrandstetter：除了时区问题之外，我还想向您询问一件事情。上述的8行代码需要很长时间，我能用其他方法使它更快吗？ - Chanti

Answer 1

timestamp的答案

您需要了解数据类型timestamp（没有时区的时间戳）和timestamptz（带有时区的时间戳）的本质。如果您不了解，请先阅读以下内容：

• 在Rails和PostgreSQL中完全忽略时区

AT TIME ZONE结构将timestamp转换为timestamptz，这几乎肯定是您的情况下错误的移动：

WHERE eventtime AT TIME ZONE 'CET' BETWEEN '2015-06-16 06:00:00'
                                       AND '2015-06-17 06:00:00'

首先，它会降低性能。将AT TIME ZONE应用于列eventtime使表达式不符合sargable的标准。Postgres无法在eventtime上使用普通索引。但即使没有索引，可搜索表达式也更便宜。调整过滤器值而不是操作每个行值。
您可以通过匹配表达式索引来进行补偿，但这可能只是一种误解，而且也是错误的。

该表达式会发生什么？

AT TIME ZONE 'CET' 通过附加当前时区的时间偏移将 timestamp 值 eventtime 转换为 timestamptz。当使用时区名称（而不是数字偏移或缩写）时，它还考虑了 DST 规则（夏令时），因此您会得到“冬季”时间戳的不同偏移量。基本上，您可以得到以下问题的答案：

在给定时区中，给定时间戳的相应 UTC 时间戳是什么？

在向用户显示结果时，它被格式化为带有会话当前时区的相应时间偏移的本地时间戳。（可能与表达式中使用的时区不同）。

右侧的字符串文字没有数据类型，因此类型是从表达式中的赋值推导出来的。由于现在是timestamptz，所以两者都被转换为timestamptz，假设当前会话的时区设置。

在当前会话的时区设置下，给定时间戳的相应 UTC 时间戳是什么？

偏移量可以随 DST 规则而变化。

长话短说，如果你总是使用相同的时区： CET 或 'Europe/Berlin' - 对于现代时间戳来说是一样的，但对于历史或（可能）未来的时间戳来说不是，你可以简化代码。

第二个问题与表达式有关：BETWEEN在使用timestamp值时几乎总是错误的。请参见：

• 优化BETWEEN日期语句
• 在PostgreSQL中查找重叠的日期范围

SELECT date_trunc('hour', eventtime) AS hour
     , count(DISTINCT serialnumber)  AS ct  -- sure you need distinct?
FROM   t_el_eventlog
WHERE  eventtime >= now()::date - interval '18 hours'
AND    eventtime <  now()::date + interval '6 hours'
AND    sourceid  =  44  -- don't quote the numeric literal
GROUP  BY 1
ORDER  BY 1;

now()是Postgres实现的SQL标准CURRENT_TIMESTAMP。两者都返回timestamptz（不是timestamp！）。您可以使用任何一个。
now()::date等同于CURRENT_DATE。两者都取决于当前时区设置。

您应该拥有以下形式的索引：

CREATE INDEX foo ON t_el_eventlog(sourceid, eventtime)

或者，为了允许仅索引扫描：

CREATE INDEX foo2 ON t_el_eventlog(sourceid, eventtime, serialnumber)

如果您在不同的时区操作，事情会变得更加复杂，您应该对所有内容使用 timestamptz。

timestamptz 的替代方案

在问题更新之前，似乎时区很重要。当处理不同的时区时，“今天”是当前时区的一个功能依赖。人们往往会忘记这一点。

为了只使用会话的当前时区设置，使用与上面相同的查询。如果在不同的时区执行，则实际上结果是错误的。（适用于上述内容。）

为了保证给定时区（在您的情况下为“Europe/Berlin”）的正确结果，而不考虑会话的当前时区设置，请改用此表达式：

    ((now() AT TIME ZONE 'Europe/Berlin')::date - interval '18 hours')
            AT TIME ZONE 'Europe/Berlin'  -- 2nd time to convert back

请注意，AT TIME ZONE 构造返回 timestamptz 输入的 timestamp，反之亦然。

正如一开始提到的那样，所有细节都在这里：

• 在 Rails 和 PostgreSQL 中完全忽略时区

- Erwin Brandstetter

---

但是为什么你的解决方案需要使用 generate_series 进行每小时的拆分呢？通常，表达式 tstz AT TIME ZONE 'tz' BETWEEN ts_lower AND ts_upper 可以更简单地进行 sargable 处理，例如：tstz BETWEEN ts_lower AT TIME ZONE 'tz' AND ts_upper AT TIME ZONE 'tz'。 - pozs

@pozs：按小时分解是一种方法。我已经在更新的解决方案上工作了 - 就像你暗示的那样。 - Erwin Brandstetter

请考虑重新修订您的问题更新后的答案。 - Erwin Brandstetter

Answer 2

您可以使用 CURRENT_DATE：

 select date_trunc('hour', t_el_eventlog.eventtime at time zone 'CET') as hours,
        count(distinct t_el_eventlog.serialnumber) as count
 from t_el_eventlog
 where eventtime at time zone 'CET' between CURRENT_DATE + interval '6 hour' and
                                            CURRENT_DATE + interval '30 hour' and
       sourceid = '44'
 group by hours
 order by hours asc;

编辑:

Erwin的评论是关于问题而不是这个回答。在处理日期/时间时使用between是一个坏主意。我想这应该在每个提出这个问题的地方都要重复一遍。但问题是，日期/时间值作为天数之间的边界被计算了两次。

正确的逻辑是:

 select date_trunc('hour', t_el_eventlog.eventtime at time zone 'CET') as hours,
        count(distinct t_el_eventlog.serialnumber) as count
 from t_el_eventlog
 where eventtime at time zone 'CET' >= CURRENT_DATE + interval '6 hour' and
       eventtime at time zone 'CET' < CURRENT_DATE + interval '30 hour' and
       sourceid = '44'
 group by hours
 order by hours asc;

请注意第二个限制的“<”符号。 这里是有关此主题的好博客。尽管Aaron专注于SQL Server，但警告（以及一些解决方案）也适用于其他数据库。

- Gordon Linoff

---

谢谢Gordon...它很好地工作了，知道了当前的工作日输出。 - Chanti

但是我想要的是前一天的输出，我尝试过像这样的代码 CURRENT_DATE + interval '6 hour' 和 CURRENT_DATE - interval '18 hour'。 - Chanti

你正在包含上限，创建了一个特殊情况的第25个小时。而且你完全忽略了时区和实际数据类型。这也不是可搜索的，因此非常低效。我怀疑投票支持这一点的人是否已经深思熟虑。 - Erwin Brandstetter

@ErwinBrandstetter……我知道这一点。这是为了实现OP所要求的查询而设计的。你应该在问题上添加那个评论。 - Gordon Linoff

好的。问题源于问题本身。但是领先的用户可能不应该在没有任何提示的情况下复制明显的错误。毫无戒心的访客会以善意的方式复制它。 - Erwin Brandstetter