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

Subtract hours from the now() function

提问by Chanti

We have a machine running 24x7. Every day I report the number of pieces it produced per hour. In our case one working day means '2015-06-16 06:00:00' to '2015-06-17 06:00:00' for example.

我们有一台 24x7 全天候运行的机器。每天我都会报告它每小时生产的件数。例如，在我们的例子中，一个工作日意味着“2015-06-16 06:00:00”到“2015-06-17 06:00:00”。

Here is my code:

这是我的代码：

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

• My Postgres version: "PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 32-bit"

• The data types of two columns which I am dealing with:

  eventtime timestamp without time zone
  sourceid  integer NOT NULL
  

• Time zone is "Europe/Berlin".

• 我的 Postgres 版本：“PostgreSQL 9.4.1，由 Visual C++ build 1800 编译，32 位”

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

  eventtime timestamp without time zone
  sourceid  integer NOT NULL
  

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

With the above query I get the information I want, but I have to change the date every day. Is it possible to use the now()function as default value for my case instead, so that I don't have to change the date manually everyday?

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

回答by Erwin Brandstetter

Answer for timestamp

回答 timestamp

You need to understand the nature of the data types timestamp without time zoneand timestamp with time zone(names can be deceiving). If you don't, read this first:

您需要了解数据类型timestamp without time zone和timestamp with time zone（名称可能具有欺骗性）的性质。如果没有，请先阅读以下内容：

• Ignoring timezones altogether in Rails and PostgreSQL

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

The AT TIME ZONEconstruct transforms your timestampto timestamptz, which is almost certainly the wrong move:

该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 ZONEto eventtimemakes the expression not sargable. Postgres cannot use a plain index on eventtime. But even without index, sargable expressions are cheaper. Provide bounds adjusted to the values in the table, so you don't have to manipulate every row.
You couldcompensate with a matching expression index, but it's probably just a misunderstanding and wrong anyway.

首先，它会扼杀性能。应用AT TIME ZONE到eventtime品牌的表达不优化搜索。Postgres 不能在 上使用普通索引eventtime。但即使没有索引，sargable 表达式也更便宜。提供根据表中的值调整的边界，因此您不必操作每一行。
您可以使用匹配的表达式索引进行补偿，但这可能只是一种误解和错误。

What happens in that expression?

在那个表达式中会发生什么？

1. AT TIME ZONE 'CET'transforms the timestampvalue eventtimeto timestamptzby appending the time zone offset of your current time zone. This takes DST (daylight saving time) into account, so you get a different offset for winter timestamps. Basically you get the answer to the question:

   What's the absolute time (UTC timestamp) when the given time zone sees the given timestamp?

   When displayingthe result to the user it becomes the according local timestamp for the current time zone of the session with the according time zone offset appended. (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 they intended type is derived from the assignment in the expression. Since we effectively have timestamptznow, both are cast to timestamptz, assuming the current time zone of the session.

   Give me the UTC timestamp for the moment in time, when the local time looks like the given timestamp.

   The offset varies with DST rules.

1. AT TIME ZONE 'CET'该转换timestamp值eventtime，以timestamptz通过附加的时区当前时区偏移。这将 DST（夏令时）考虑在内，因此您将获得不同的冬季时间戳偏移量。基本上你会得到这个问题的答案：

   当给定的时区看到给定的时间戳时，绝对时间（UTC 时间戳）是多少？

   当向用户显示结果时，它将成为会话当前时区的相应本地时间戳，并附加相应的时区偏移量。（可能与表达式中使用的相同，也可能不同）。

2. 右侧的字符串文字没有数据类型，因此它们的预期类型来自表达式中的赋值。由于我们timestamptz现在有效timestamptz，因此假设会话的当前时区，两者都被强制转换为 。

   当本地时间看起来像给定的时间戳时，给我当时的 UTC 时间戳。

   偏移量随 DST 规则而变化。

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

长话短说，如果您在任何地方都使用相同的时区：CET或者'Europe/Berlin'，对于当前的时间戳，但不适用于历史或（可能）未来的时间戳，您可以减少 cruft。

The second problemwith the expression: BETWEENis almost always wrong with timestampvalues. Details:

表达式的第二个问题：BETWEEN值几乎总是错误的timestamp。细节：

• Optimize BETWEEN date statement
• Find overlapping date ranges in PostgreSQL

• 优化 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()is the Postgres implementation of the SQL standard CURRENT_TIMESTAMP. Both return timestamptz(not timestamp!). You can use either.
now()::dateis equivalent to CURRENT_DATE. Both depend of the current time zone setting.

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

You should have an indexof the form:

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

CREATE INDEX foo ON t_el_eventlog(sourceid, eventtime)

Or, to allow index-only scans:

或者，要允许仅索引扫描：

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

If you operate in different time zones, things get more complicated and you should use timestamptzfor everything.

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

Alternative for timestamptz

替代品 timestamptz

Before the question update, it seemed like time zones matter. When dealing with different time zones, "today"is a functional dependency of the current time zone. People tend to forget that.

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

To just work with the current time zone setting of the session, use the same query as above. If executed in a different time zone, the results are wrong in actuality. (Applies to the above as well.)

要仅使用会话的当前时区设置，请使用与上述相同的查询。如果在不同的时区执行，结果实际上是错误的。（同样适用于上述情况。）

To guarantee a correct result for a given time zone ('Europe/Berlin' in your case) irregardless of the current time zone setting of the session, use this expression instead:

无论会话的当前时区设置如何，为了保证给定时区（在您的情况下为“欧洲/柏林”）的正确结果，请改用以下表达式：

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

Be aware that the AT TIME ZONEconstruct returns timestampfor timestamptzinput and vice-versa.

请注意，AT TIME ZONE构建回报timestamp的timestamptz投入，反之亦然。

As mentioned at the outset, all the gory details here:

正如开头所提到的，这里的所有血腥细节：

• Ignoring timezones altogether in Rails and PostgreSQL

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

回答by Gordon Linoff

Your can use CURRENT_DATE:

您可以使用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;

EDIT:

编辑：

Erwin's comment is about the questionnot this answer. Using betweenfor date/times is a bad idea. I suppose this should be repeated in every question that does this. But the problem is that the date/time values that are boundaries between days are counted twice.

欧文的评论是关于问题而不是这个答案。使用between日期/时间是一个坏主意。我想这应该在每个这样做的问题中重复。但问题是作为天之间边界的日期/时间值被计算两次。

The correct logic is:

正确的逻辑是：

 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;

Note the "<" for the second limit. Hereis a good blog on this subject. Although Aaron is focused on SQL Server, the warnings (and some of the solutions) apply to other databases as well.

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