handling rails + postgres and timezones

Question

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.

ere · Accepted Answer · 2013-10-03 16:14:06Z

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)

answered Oct 3, 2013 at 16:14

ere

1,7793 gold badges19 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt Johnson-Pint · Accepted Answer · 2013-10-03 23:59:49Z

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.

answered Oct 3, 2013 at 23:59

Matt Johnson-Pint

244k75 gold badges468 silver badges621 bronze badges

Comments

Ramon Carlos · Accepted Answer · 2016-11-08 01:35:23Z

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

answered Nov 8, 2016 at 1:35

Ramon Carlos

651 silver badge4 bronze badges

Collectives™ on Stack Overflow

handling rails + postgres and timezones

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related