这是我的代码：
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 build编写,32位”
>我正在处理的两列数据类型：
eventtime timestamp without time zone sourceid integer NOT NULL
>时区是“欧洲/柏林”.
通过上面的查询,我得到了我想要的信息,但我必须每天更改日期.是否可以使用now()函数作为我的情况的默认值,这样我就不必每天手动更改日期？
解决方法
您需要了解数据类型时间戳的性质,没有时区和带时区的时间戳(名称可能是欺骗性的).如果你不这样做,请先阅读：
> Ignoring timezones altogether in Rails and PostgreSQL
AT TIME ZONE构造将您的时间戳转换为timestamptz,这几乎肯定是错误的举动：
where eventtime at time zone 'CET' between '2015-06-16 06:00:00' and '2015-06-17 06:00:00'
首先,它会杀死性能.将AT TIME ZONE应用于eventtime使得表达式不是sargable.Postgres不能在事件时使用普通索引.但即使没有索引,sargable表达式也会更便宜.提供根据表中的值调整的边界,这样您就不必操纵每一行.
您可以使用匹配的表达式索引进行补偿,但无论如何它可能只是一个误解和错误.
那个表达会发生什么？
> AT TIME ZONE’CET’通过附加当前时区的时区偏移量将时间戳值事件时间转换为timestamptz.这会将DST(夏令时)考虑在内,因此您可以获得冬季时间戳的不同偏移量.基本上你得到了问题的答案：
当给定时区看到给定时间戳时,绝对时间(UTC时间戳)是多少？
当向用户显示结果时,它将成为会话当前时区的相应本地时间戳,并附加相应的时区偏移量. (可能与表达式中使用的相同或不同).
>右侧的字符串文字没有数据类型,因此它们的类型是从表达式中的赋值派生的.由于我们现在有效地拥有时间戳,所以假设当前时区都被转换为时间戳.
当地时间看起来像给定的时间戳时,给我当时的UTC时间戳.
偏移量随DST规则而变化.
简而言之,如果你在任何地方都使用相同的时区：CET或“欧洲/柏林”,对于现在的时间戳来说,同样的事情,但不是历史性的或(可能的)未来的时间段,你可以减少残余.
表达式的第二个问题：BETWEEN几乎总是错误的时间戳值.细节：
> 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(不是时间戳！).你可以使用其中之一.
now():: date相当于CURRENT_DATE.两者都取决于当前时区设置.
你应该有一个表格的索引：
CREATE INDEX foo ON t_el_eventlog(sourceid,eventtime)
或者,允许仅索引扫描：
CREATE INDEX foo2 ON t_el_eventlog(sourceid,eventtime,serialnumber)
如果你在不同的时区操作,事情变得更复杂,你应该使用timestamptz来做所有事情.
时间戳的替代方案
在问题更新之前,似乎时区很重要.处理不同时区时,“今天”是当前时区的功能依赖.人们往往会忘记这一点.
要仅使用会话的当前时区设置,请使用与上面相同的查询.如果在不同的时区执行,结果实际上是错误的. (也适用于上述内容.)
为了保证给定时区的正确结果(在您的情况下为“Europe / Berlin”),无论会话的当前时区设置如何,请使用以下表达式：
((now() AT TIME ZONE 'Europe/Berlin')::date - interval '18 hours')
AT TIME ZONE 'Europe/Berlin' -- 2nd time to convert back
请注意,AT TIME ZONE构造返回timestamptz输入的时间戳,反之亦然.
正如一开始所提到的,这里所有的血腥细节：
