从now（）函数减去小时数 [英] Subtract hours from the now() function

问题描述

我们有一台运行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 
其中eventtime在'2015-06-16 06:00:00'
和'2015之间的时区'CET' -06-17 06:00:00'
 and sourceid ='44'
 group by hours 
 order by hours asc 
  

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

  / li>
  

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

    eventtime没有时区的时间戳
   sourceid integer NOT NULL 
    




• 时区是欧洲/柏林。

使用上述查询，我​​得到我想要的信息，但我必须每天更改日期。可以使用 now（）函数作为我的case的默认值，这样我不必每天手动更改日期？

解决方案

时间戳的答案

您需要了解数据类型的特性时间戳没有时区和时区戳与时区（名称可以是欺骗）。如果没有，请先阅读：

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

AT TIME ZONE 构造将 timestamp 转换为

  <罢工> 
 >2015-06-16 06:00:00
与2015-06-17 06:00:00之间的时区CET的事件时间  

首先，会导致性能下降。将 AT TIME ZONE 应用于 eventtime 使表达式不会 sargable 。 Postgres不能在 eventtime 上使用一个简单的索引。但即使没有索引，可用的表达式更便宜。提供调整到表中的值的边界，所以您不必操纵每一行。

您可以使用匹配的表达式索引进行补偿，但这可能只是一个误会而且无论如何，

该表达式会发生什么？

1. 转换时间戳值事件时间到 timestamptz ，通过附加当前时区的时区偏移量。这需要考虑DST（夏令时），因此您可以为冬季时间戳获得不同的偏移量。基本上你会得到以下问题的答案：

   
   
   

   当给定的时区看到给定的时间戳时，什么是绝对时间（UTC时间戳）？ / p>
   
   

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




2. 右侧的字符串文字没有数据类型，所以它们的意图类型是从表达式中的赋值导出的。由于我们现在有效地拥有 timestamptz ，假设会话的当前时区，都将转换为 timestamptz p>
   
   

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

   
   
   

   偏移量与DST规则不同。

长篇小说如果您在任何地方使用相同的时区： CET 或'Europe / Berlin'，同样的事情，日期时间戳，但不是历史或（可能）未来的时间戳，您可以直接剪切该片段。

第二个问题的表达式：在 timestamp 值中，几乎总是出现 BETWEEN 。详细信息：

• 优化BETWEEN日期语句


• 在PostgreSQL中查找重叠日期范围

  SELECT date_trunc（'hour'，eventtime）AS小时
，count（DISTINCT序列号）AS ct  - 确定你需要明确吗？ 
 FROM t_el_eventlog 
 WHERE eventtime> = now（）:: date  -  interval '18 hours'
 AND eventtime< now（）:: date + interval'6 hours'
 AND sourceid = 44  - 不引用数字文字
 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

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

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

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

 （（now（）AT TIME ZONE'Europe / Berlin'）::日期 - 间隔'18小时'）
在时区欧洲/柏林 - 第二次转换
  

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

一开始就提到，所有这里的血腥细节：

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

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.

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".

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?

解决方案

Answer for timestamp

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

• Ignoring timezones altogether in Rails and PostgreSQL

The AT TIME ZONE construct transforms your timestamp to timestamptz, which is almost certainly the wrong move:

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 eventtime makes 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 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 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 displaying the 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 timestamptz now, 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.

Long story short, if you operate with the same time zone everywhere: 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. Details:

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

You should have an index of 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 timestamptz for everything.

Alternative for 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 ZONE construct returns timestamp for timestamptz input and vice-versa.

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

• Ignoring timezones altogether in Rails and PostgreSQL

这篇关于从now（）函数减去小时数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！