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

问题描述

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

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.

这是我的代码:

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

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

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

    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?

    推荐答案

    timestamp

    的答案

    您需要了解数据类型 timestamp(timestamp without time zone)和 timestamptz(timestamp with time区).如果没有，请先阅读以下内容:

    Answer for timestamp

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

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

    AT TIME ZONE 构造将 timestamp 转换为 timestamptz，这几乎肯定是 错误的移动你的情况:

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

    where eventtime at time zone 'CET' between '2015-06-16 06:00:00'
                                           and '2015-06-17 06:00:00'

    首先，它会影响性能.将 AT TIME ZONE 应用于列 eventtime 使得表达式 not sargable.Postgres 不能在 eventtime 上使用纯索引.但即使没有索引，sargable 表达式也更便宜.调整过滤器值，而不是操纵每一行值.
    您可以用匹配的表达式索引来弥补，但这可能只是一种误解和错误.

    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' 将 timestamp 值 eventtime 通过附加您当前时区的时间偏移量.当使用时区名称(不是数字偏移或缩写)时，这也会考虑 DST 规则(夏令时)，因此您会获得不同的冬季"偏移.时间戳.基本上你得到了问题的答案:

    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:

    给定时区的给定时间戳对应的 UTC 时间戳是多少?

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

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

    右边的字符串文字没有数据类型，所以类型是从表达式中的赋值派生的.由于现在是 timestamptz，因此两者都被强制转换为 timestamptz，假设会话的当前时区.

    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.

    当前会话的时区设置的给定时间戳对应的 UTC 时间戳是多少.

    偏移量可能因 DST 规则而异.

    The offset can vary with DST rules.

    长话短说，如果您总是在同一时区操作:CET 或 'Europe/Berlin' - 现在的时间戳也是一样的，但对于历史或(可能)未来的时间戳不一样，你可以省去麻烦.

    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.

    第二个问题与表达式:BETWEEN 几乎总是与 timestamp 值 错误.见:

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

    • 优化 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() 是 SQL 标准 CURRENT_TIMESTAMP 的 Postgres 实现.两者都返回 timestamptz(不是 timestamp！).你可以使用任何一个.
    now()::date 等价于 CURRENT_DATE.两者都取决于当前时区设置.

    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 on the current time zone setting.

    你应该有一个索引的形式:

    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)
    

    如果你在不同的时区操作，事情会变得更加复杂，你应该使用 timestamptz 来处理所有事情.

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

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

    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
    

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

    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:

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

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