1

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.

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.

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.

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)

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 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.

holden
  • 13,471
  • 22
  • 98
  • 160

3 Answers3

4

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

Time.zone.tzinfo.identifier

Or, in your example:

User.events.where("EXTRACT(dow FROM start_at AT TIME ZONE ?) = ?", Time.zone.tzinfo.identifier, Date.today.wday)
ere
  • 1,739
  • 3
  • 19
  • 41
1

Try using the Ruby TZInfo gem directly, instead of using Rails ActiveSupport::TimeZone.

Alternatively, use the MAPPING constant, as shown in the ActiveSupport::TimeZone documentation, which will take you from a Rails time zone key back to the standard IANA time zone identifier used by Postgres and others.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
0

As Matt Johnson suggested use TZInfo gem directly. This way you can get the correctly formatted time zone identifiers you need to query with PostgreSQL.

For example if you use:

TZInfo::Timezone.all_country_zone_identifiers

This will return an array of correct IANA/Olson time zone identifiers. In other words you will get the correct 'Europe/Warsaw' NOT 'Europe - Warsaw'.

Ramon Carlos
  • 65
  • 1
  • 4