RoR Postgresql时区组在Heroku上不起作用 [英] RoR Postgresql timezone group by not working on Heroku

问题描述

我希望我的报告在CST中显示，而我的数据库使用UTC。为了这个工作，我需要能够按时间列进行分组，并让数据库执行时区转换。

I would like my reports to display in CST while my database is in UTC. For this to work I need to be able to group by a time column and have the db do timezone conversions.

这适用于我的开发postgresql框：

This works on my dev postgresql box:

    offset = -5
    result = ActiveRecord::Base.connection.execute("select  date(srl.created_at AT TIME
    ZONE '#{offset.to_s}') AS date, count(*) from server_request_logs srl where     
    srl.created_at between '#{@from.to_s(:db)}' and '#{@to.to_s(:db)}' group by 
    date(srl.created_at AT TIME ZONE '#{offset.to_s}')")

on heroku错误：

on heroku it errors with:

ActiveRecord::StatementInvalid: PGError: ERROR:  time zone "-5" not recognized

它也不适用于TZInfo名称，如'America / Winnipeg '或从 http://www.postgresql.org/支持'CST'等时区文档/ 7.2 /静态/ timezones.html

Its also not working with TZInfo names like 'America/Winnipeg' or supported timezones like 'CST' from http://www.postgresql.org/docs/7.2/static/timezones.html

任何人都可以得到这个工作吗？

has anyone been able to get this working?

推荐答案

，请确保您将时间戳列和变量定义为 TIMESTAMP WITH TIME ZONE （或 timestamptz ）。在PostgreSQL中，这实际上并不会导致任何时间戳被保存;但使其成为一个固定的时间点，并以UTC存储。你可以用干净的语义来查看它< AT TIME ZONE 。 TIMESTAMP WITHOUT TIME ZONE （如果你只是说 TIMESTAMP ）就是 not 一个固定的时间点，直到解决时区为止，因此很难处理。

First off, make sure that you define your timestamp columns and variables as TIMESTAMP WITH TIME ZONE (or timestamptz for short). In PostgreSQL this doesn't actually cause any time stamp to be saved; but makes it a fixed point in time, stored in UTC. You can view it AT TIME ZONE of your choosing with clean semantics. TIMESTAMP WITHOUT TIME ZONE (which is what you get if you just say TIMESTAMP) is not a fixed point in time until it is resolved against a time zone, and is therefore much harder to work with.

您引用的有关时区的文档页面来自非常旧的PostgreSQL版本已经失去支持。也许这个网页对您有更多帮助：

The documentation page you cite regarding time zones is from a very old version of PostgreSQL which has gone out of support. Maybe this page will be of more help to you:

http://www.postgresql.org/docs/current/interactive/datetime-config-files.html

这篇关于RoR Postgresql时区组在Heroku上不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！