Ruby, Postgres, Sequel and Time Zones

Needed to work with different time zones with Postgres today. Looking at a UTC timestamp in New York means that this:

select now();

Should really look like this:

select now() at time zone 'America/New_York';

However, I want to do this in Ruby using the Sequel Gem. The good news is that adding new SQL language constructs is very simple.

First, I added the following to an initializer in my Rails app:

module Sequel
  module SQL
    AT_TIMEZONE = [
      "(".freeze, " at time zone ".freeze, ")".freeze
    ].freeze

    module ComplexExpressionMethods
      def at_timezone(timezone)
        Sequel::SQL::PlaceholderLiteralString.new(
          AT_TIMEZONE, [self, timezone]
        )
      end
    end
  end
end

Now, I can do the following in in Ruby:

DB.select { now {}.at_timezone('America/New_York') }

References

Postgres 9.2 - AT TIME ZONE Syntax
Ruby Sequel - ComplexExpressionMethods Module