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

我们有一台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()函数用作我的案例的默认值，这样我就不必每天手动更改日期了？

最佳答案

回答timestamp您需要了解数据类型timestamp(timestamp without time zone)和timestamptz(timestamp with time zone)的性质。如果不这样做，请先阅读以下内容:

Ignoring time zones altogether in Rails and PostgreSQL

AT TIME ZONE构造将timestamp转换为timestamptz，这几乎可以肯定是您的情况下错误地移动的原因:

首先是，它会降低性能。将AT TIME ZONE应用于eventtime列将使表达式不是sargable 。 Postgres不能在eventtime上使用普通索引。但是即使没有索引，可修饰的表达式也更便宜。调整过滤器值，而不要处理每个行值。
您可以使用匹配的表达式索引进行补偿，但是无论如何这可能只是一个误解和错误。
在那个表情中会发生什么？

AT TIME ZONE 'CET'通过附加当前时区的时间偏移将timestamp值eventtime转换为timestamptz。当使用时区名称(不是数字偏移量或缩写)时，这也会考虑DST规则(夏令时)，因此您会为“冬季”时间戳记获得不同的偏移量。基本上，您会得到以下问题的答案:
给定时区中给定时间戳的对应UTC时间戳是什么？
当向用户显示结果时，将其格式化为本地时间戳，并具有 session 当前时区的相应时间偏移。 (可能与表达式中使用的相同或不同)。

右侧的字符串文字没有数据类型，因此该类型是从表达式中的赋值派生的。由于现在是timestamptz，因此将两者都转换为timestamptz，并假定 session 的当前时区。
给定时间戳的当前 session 时区设置对应的UTC时间戳是多少？
偏移量可能会因DST规则而异。

长话短说，如果您始终在相同的时区进行操作:CET或'Europe/Berlin'-对于当前时间戳而言，是相同的，但对于历史时间戳或(可能)将来的时间戳而言，则不是这样，您可以减少工作量。
表达式 is almost always wrong with timestamp values的第二个问题。看:

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 构造返回timestamp输入的timestamptz，反之亦然。
正如开头提到的，所有血腥细节都在这里:

Ignoring time zones altogether in Rails and PostgreSQL