0

Rails 4.2 and PostgreSQL 9.4.

I have events with starts_at and time_zone columns.

I'd like a SQL query to return all events that start, in their local time zone, on a specified date.

I.e. if an event starts at 11pm in Hawaii on Jan 24th:

t1 = ActiveSupport::TimeZone['Hawaii'].parse("2015-01-24 23:00:00")
=> Sat, 24 Jan 2015 23:00:00 HST -10:00
t1.in_time_zone('UTC')
=> Sun, 25 Jan 2015 09:00:00 UTC +00:00

And if an event starts at 1am in Hong Kong on Jan 24th:

t2 = ActiveSupport::TimeZone['Hong Kong'].parse("2015-01-24 01:00:00")
=> Sat, 24 Jan 2015 01:00:00 HKT +08:00
t2.in_time_zone('UTC')
=> Fri, 23 Jan 2015 17:00:00 UTC +00:00

The events occur on the 25th and 23rd of Jan in Rails' implicit UTC database zone, but both events occur on the 24th in their own time zone.

I'd like to write a SQL query akin to:
SELECT * FROM events WHERE (events.starts_at at time zone 'UTC' at time zone events.time_zone)::date = '2015-01-24';
(The initial conversion to UTC is required for a timestamp I believe)

However I get an error ERROR: time zone "Hawaii" not recognized

This is because PostgreSQL time zone names do not match with that of Rails (SELECT * FROM pg_timezone_names; for a list.)

Is there an alternative method where I can still do the query in SQL but not have this problem? I don't want to load the entire events set into Rails and process it there as it is very large and this is a frequent query.

As a small context as to why I need this query: I have a membership pass valid over a certain date range which gives you access to events which can occur in different time zones.

If there is no other way to do this, is there any tool that converts between the Rails names for time zones and that used by PostgreSQL so that I can save both the Rails zone and the PostgreSQL zone in the database record?

Using time zone abbreviations such as PDT instead (I don't even know if these are consistent between Rails and PostgreSQL either) doesn't deal with daylight savings time (which, granted, may not be an issue here but is not satisfactory).

Ben Smith
  • 851
  • 2
  • 10
  • 22
  • 1
    See [this question](http://stackoverflow.com/questions/19397663/how-do-i-get-rails-timezone-names-from-obsolete-tzinfo-identifiers). – Marth Jan 24 '15 at 22:24
  • So your `starts_at` column is a `timestamp [without time zone]`, and you have an additional column for storing time zone? Why not storing your time instances in a single `timestamp with time zone`? – pozs Jan 26 '15 at 09:04
  • @pozs: The 'Rails Way' is to use `timestamp [without time zone]` with an implicit convention that all times are in UTC. It does a lot of automatic conversion 'magic', so switching to `timestamptz` will have some overhead in investigating the side effects on the existing code base. – Ben Smith Jan 27 '15 at 00:00
  • @BenSmith then this question might interest you http://stackoverflow.com/questions/23420859/how-to-map-rails-timezone-names-to-postgresql -- the method there is in ruby, but you could build up a mapping table from the data there (or you could store the mapped time-zone already, and if you need to work with those in ruby, "unmap" them). – pozs Jan 27 '15 at 08:54

0 Answers0