Setting PostgreSQL Time Zone in Hanami 2.2 app

1

Hey :waving_hand:

In my Hanami 2 project, Iā€™m using postgreSql and want timestamps returned in a specific time zone (Europe/Vienna), not UTC. A postgreSql-server is set to UTC by default, which makes sense, cause it is future proof. So I want to let it untouched. postgreSql handles this perfectly with timestamptz, but you need to set the time zone for each DB connection using SET TIME ZONE.

To make that work in Hanami, you have to customize the db provider.

# frozen_string_literal: true

Hanami.app.configure_provider :db do
  config.gateway :default do |gw|
    gw.connection_options after_connect: proc { |conn| conn.exec("SET TIME ZONE 'Europe/Vienna'") }
  end
end

So my question:
Should this use case be supported out of the box without custom after_connect config?
How do you deal with this? I believe that this use-case occurs most of the time, or am I wrong?

The downside:
This approach relies on postgreSqls timestamptz behavior and SET TIME ZONE, which may not be portable to other databases.

Thanks

2

Do you need per-session timezones? Because there is an app-level setting:

Hanami.app.configure_provider :db do
  before :start do
    Sequel.application_timezone = :local
  end
end

Set TZ=Europe/Vienna in your system environment when loading the app, you should see:

bookshelf[development]> app["db.gateway"].connection.fetch("select current_timestamp").to_a
[bookshelf] [INFO] [2025-06-06 02:32:41 +0200]   Loaded :postgres in 1ms select current_timestamp
=> [{current_timestamp: 2025-06-06 02:32:41.480931 +0200}]
2 Likes
3

Thanks @alassek.
This is a much better solution.