sql – 从now()函数中减去小时数
我们有一台24×7全天候运行的机器.我每天都会报告每小时生产的件数.在我们的例子中,一个工作日意味着’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 build编写,32位” eventtime timestamp without time zone sourceid integer NOT NULL >时区是“欧洲/柏林”. 通过上面的查询,我得到了我想要的信息,但我必须每天更改日期.是否可以使用now()函数作为我的情况的默认值,这样我就不必每天手动更改日期? 解决方法回答时间戳您需要了解数据类型时间戳的性质,没有时区和带时区的时间戳(名称可能是欺骗性的).如果你不这样做,请先阅读: > Ignoring timezones altogether in Rails and PostgreSQL AT TIME ZONE构造将您的时间戳转换为timestamptz,这几乎肯定是错误的举动:
首先,它会杀死性能.将AT TIME ZONE应用于eventtime使得表达式不是sargable.Postgres不能在事件时使用普通索引.但即使没有索引,sargable表达式也会更便宜.提供根据表中的值调整的边界,这样您就不必操纵每一行. 那个表达会发生什么? > AT TIME ZONE’CET’通过附加当前时区的时区偏移量将时间戳值事件时间转换为timestamptz.这会将DST(夏令时)考虑在内,因此您可以获得冬季时间戳的不同偏移量.基本上你得到了问题的答案: 当给定时区看到给定时间戳时,绝对时间(UTC时间戳)是多少? 当向用户显示结果时,它将成为会话当前时区的相应本地时间戳,并附加相应的时区偏移量. (可能与表达式中使用的相同或不同). 当地时间看起来像给定的时间戳时,给我当时的UTC时间戳. 偏移量随DST规则而变化. 简而言之,如果你在任何地方都使用相同的时区:CET或“欧洲/柏林”,对于现在的时间戳来说,同样的事情,但不是历史性的或(可能的)未来的时间段,你可以减少残余. 表达式的第二个问题:BETWEEN几乎总是错误的时间戳值.细节: > Optimize BETWEEN date statement 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;
你应该有一个表格的索引: 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 请注意, 正如一开始所提到的,这里所有的血腥细节: > Ignoring timezones altogether in Rails and PostgreSQL (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |