处理rails + postgres和时区 [英] handling rails + postgres and timezones

问题描述

我有一个使用许多不同时区的应用程序...它将它们设置在控制器中，并且它们会根据用户而变化。所有时间都存储在UTC中，没有时间戳等。

I have an application which uses many different timezones... it sets them in a controller and they change depending on the user. All the times are stored in UTC without a timestamp etc.

我的理解是，这是Rails处理时区的正常方法。直到我需要直接使用Postgres做某事之前，这在99％的时间内都可以正常工作，然后时区就成了问题。

My understanding is this is the normal way for Rails to handle timezones. This works fine 99% of the time until i need to do something directly with Postgres then the Timezone becomes a problem.

例如，该查询是完全错误的，但GMT除外，例如在中央时区，根据时间设置，它会得出错误的日期：

For example this query is completely wrong except for GMT, for example in Central Time Zone, depending on the hour set, it gets the wrong day:

Events.where("EXTRACT(dow FROM start_at)", Date.today.wday)

我要在其中查找所有日期是某一天。

Where I'm trying to find all the dates that fall on a certain day.

我正在尝试执行此类操作。我不需要在时区之间进行搜索（不会混用），但是如果不是UTC，则需要指定时区以获得正确的结果。

I'm trying to do something like this. I don't need to search between timezones (they won't be mixed), but I do need to specify the timezone if it's not UTC to get correct results.

User.events.where("EXTRACT(dow FROM start_at AT TIME ZONE ?) = ?", 'UTC', Date.today.wday)

但是我不确定如何使用 Time.zone 给我一些东西

But I'm not sure how to use Time.zone to give me something that will work with TIME ZONE in Postgres.

Time.zone.tzinfo有时可以使用... Postgres可以与欧洲/华沙一起使用，但Rails返回欧洲-华沙

Time.zone.tzinfo sometimes works... Postgres will work with 'Europe/Warsaw' but Rails returns 'Europe - Warsaw'

总的来说，我对时区不太满意，任何指针都将不胜感激。

In general I'm not having much luck with timezones, any pointers would be appreciated.

推荐答案

也许其他人可以提供更好的整体解决方案，但是特定查询所需的条件是

Maybe someone else has a better overall solution, but what you need for the particular query is

Time.zone.tzinfo.identifier

或者在您的示例中：

User.events.where("EXTRACT(dow FROM start_at AT TIME ZONE ?) = ?", Time.zone.tzinfo.identifier, Date.today.wday)

这篇关于处理rails + postgres和时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！