如何使用Ecto查询获取数据库时区？除SQL.query!外有无其他方法？

Great question! While SQL.query!(Repo, "SHOW TIMEZONE", []) works, there are more idiomatic Ecto approaches to fetch the database server's timezone without relying on raw ad-hoc SQL calls. Here are the most common methods:

1. Use Ecto.Query with fragment to Call Database System Functions #

This is the cleanest Ecto-native way—you’ll leverage Ecto’s query builder while still invoking the database’s built-in functions to retrieve the timezone. The exact function depends on your database:

For PostgreSQL #

PostgreSQL uses current_setting('timezone') to return the server’s active timezone. You can wrap this in a fragment and query it via Ecto:

# Get the timezone as a string (e.g., "UTC", "Europe/London")
timezone = Repo.one(from _ in fragment("SELECT current_setting('timezone')"))

Or if you prefer a more explicit alias:

query = from _ in fragment("SELECT current_setting('timezone') AS db_timezone")
%{db_timezone: timezone} = Repo.one(query)

For MySQL #

MySQL uses @@time_zone to fetch the server’s timezone. The Ecto query would look like:

timezone = Repo.one(from _ in fragment("SELECT @@time_zone"))

For SQLite #

Note: SQLite doesn’t have a dedicated server timezone—it uses the timezone of the connection (usually the host machine’s timezone). You can retrieve the local timezone offset with:

offset = Repo.one(from _ in fragment("SELECT strftime('%z', 'now')"))

2. Query Database System Tables (PostgreSQL-Specific) #

If you want more context about available timezones (not just the active one), you can query PostgreSQL’s pg_timezone_names system table, and filter to find the currently active timezone:

query = from t in "pg_timezone_names",
        where: t.name == fragment("current_setting('timezone')"),
        select: %{name: t.name, abbrev: t.abbrev, utc_offset: t.utc_offset}

Repo.one(query)

This returns a map with details like the timezone abbreviation and UTC offset, which can be useful if you need more than just the name.

Important Note #

Don’t confuse the database server timezone with Ecto’s application-level timezone. The value from Application.get_env(:your_app, YourApp.Repo)[:timezone] is the timezone Ecto uses for datetime conversions in your app, which may not match the database server’s actual timezone. Always fetch directly from the database if you need the server’s current setting.

内容的提问来源于stack exchange，提问作者MikamB