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

Question

我们有一台 24x7 运行的机器。每天我报告它每小时生产的件数。例如，在我们的例子中，一个工作日意味着“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，由 Visual C++ build 1800 编译，32 位”

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

  eventtime timestamp without time zone
  sourceid  integer NOT NULL
  

• 时区是“欧洲/柏林”。

通过上面的查询，我得到了我想要的信息，但我必须每天更改日期。是否可以使用 now() 函数作为我的案例的默认值，这样我就不必每天手动更改日期？

Answer 1

时间戳的答案

您需要了解数据类型timestamp(timestamp without time zone)和timestamptz(timestamp with time区域)。如果您不这样做，请先阅读此内容:

• Ignoring time zones altogether in Rails and 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'
  

First, it kills performance. Applying AT TIME ZONE to the column eventtime makes the expression not sargable. Postgres cannot use plain indexes on eventtime. But even without index, sargable expressions are cheaper. Adjust filter values instead of manipulating every row value.
You could compensate with a matching expression index, but it's probably just a misunderstanding and wrong anyway.

What happens in that expression?

1. AT TIME ZONE 'CET' transforms the timestamp value eventtime to timestamptz by appending the time offset of your current time zone. When using a time zone name (not a numeric offset or an abbreviation), this also takes DST rules (daylight saving time) into account, so you get a different offset for "winter" timestamps. Basically you get the answer to the question:

   What's corresponding UTC timestamp for the given timestamp in the given time zone?

   When displaying the result to the user it is formatted as local timestamp with the according time offset for the current time zone of the session. (May or may not be the same as the one used in the expression).

2. The string literals on the right side have no data type to them, so the type is derived from the assignment in the expression. Since that's timestamptz now, both are cast to timestamptz, assuming the current time zone of the session.

   What's the corresponding UTC timestamp for the given timestamp for the time zone setting of the current session.

   The offset can vary with DST rules.

Long story short, if you always operate with the same time zone: CET or 'Europe/Berlin' - same thing for present-day timestamps, but not for historic or (possibly) future ones, you can just cut the cruft.

The second problem with the expression: BETWEEN is almost always wrong with timestamp values. See:

• Optimize BETWEEN date statement
• Find overlapping date ranges in 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()是 SQL 标准 CURRENT_TIMESTAMP 的 Postgres 实现。两者都返回 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

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

要仅使用 session 的当前时区设置，请使用与上述相同的查询。如果在不同的时区执行，实际上结果是错误的。 (同样适用于上述内容。)

无论 session 的当前时区设置如何，要保证给定时区(在您的情况下为“欧洲/柏林”)的正确结果，请改用此表达式:

    ((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，反之亦然。

正如一开始提到的，这里有所有血淋淋的细节:

• Ignoring time zones altogether in Rails and PostgreSQL