postgresql 如何在 Postgres 9.3 中获取当前时区名称？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28216923/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I get the current timezone name in Postgres 9.3?
提问by Deutro
I want to get the current timezone name.
What I already achieved is to get the utc_offset/ the timezone abbreviation via:
我想获取当前的时区名称。我已经实现的是通过以下方式获取utc_offset/ 时区缩写：
SELECT * FROM pg_timezone_names WHERE abbrev = current_setting('TIMEZONE')
This gives me all Continent / Capital combinations for this timezone but not the exact timezone. For example I get:
这给了我这个时区的所有大陆/首都组合，但不是确切的timezone. 例如我得到：
Europe/Amsterdam
Europe/Berlin
The server is in Berlinand I want to get the timezone name of the server.
服务器在Berlin，我想获取服务器的时区名称。
The problem I have with CETthat it is always UTC+01:00and does not account for DST iirc.
我遇到的问题CET是它始终存在UTC+01:00并且没有考虑到DST iirc.
回答by Mike Sherrill 'Cat Recall'
I don't think this is possible using PostgreSQL alone in the most general case. When you install PostgreSQL, you pick a time zone. I'm pretty sure the default is to use the operating system's timezone. That will usually be reflected in postgresql.conf as the value of the parameter "timezone". But the value ends up as "localtime". You can see this setting with the SQL statement.
我认为在最一般的情况下单独使用 PostgreSQL 是不可能的。安装 PostgreSQL 时，您选择一个时区。我很确定默认是使用操作系统的时区。这通常会反映在 postgresql.conf 中作为参数“时区”的值。但该值最终为“localtime”。您可以使用 SQL 语句查看此设置。
show timezone;
But if you change the timezone in postgresql.conf to something like "Europe/Berlin", then show timezone;will return thatvalue instead of "localtime".
但是，如果您将 postgresql.conf 中的时区更改为“Europe/Berlin”之类的内容，show timezone;则将返回该值而不是“localtime”。
So I think your solution will involve setting "timezone" in postgresql.conf to an explicit value rather than the default "localtime".
所以我认为您的解决方案将涉及将 postgresql.conf 中的“时区”设置为显式值，而不是默认的“本地时间”。
回答by koyae
This may or may not help you address your problem, OP, but to get the timezone of the current server relative to UTC (UT1, technically), do:
这可能会也可能不会帮助您解决您的问题，OP，但要获取当前服务器相对于 UTC（UT1，技术上）的时区，请执行以下操作：
SELECT EXTRACT(TIMEZONE FROM now())/3600.0;
The above works by extracting the UT1-relative offset in minutes, and then converting it to hours using the factor of 3600 secs/hour.
上述工作通过以分钟为单位提取 UT1 相对偏移，然后使用 3600 秒/小时的因子将其转换为小时。
Example:
例子：
SET SESSION timezone TO 'Asia/Kabul';
SELECT EXTRACT(TIMEZONE FROM now())/3600.0;
-- output: 4.5 (as of the writing of this post)
(docs).
（文档）。
回答by KotGaf
It seems to work fine in Postgresql 9.5:
它似乎在Postgresql 9.5 中工作正常：
SELECT current_setting('TIMEZONE');
回答by Igoranze
See this answer: Source
看到这个答案：来源
If timezone is not specified in postgresql.conf or as a server command-line option, the server attempts to use the value of the TZ environment variable as the default time zone. If TZ is not defined or is not any of the time zone names known to PostgreSQL, the server attempts to determine the operating system's default time zone by checking the behavior of the C library function localtime(). The default time zone is selected as the closest match among PostgreSQL's known time zones. (These rules are also used to choose the default value of log_timezone, if not specified.) source
如果时区未在 postgresql.conf 中指定或作为服务器命令行选项指定，则服务器尝试使用 TZ 环境变量的值作为默认时区。如果 TZ 未定义或不是 PostgreSQL 已知的任何时区名称，则服务器尝试通过检查 C 库函数 localtime() 的行为来确定操作系统的默认时区。选择默认时区作为 PostgreSQL 已知时区中最接近的匹配项。（这些规则也被用来选择log_timezone的默认值，如果没有指定。）源
This means that if you do not define a timezone, the server attempts to determine the operating system's default time zone by checking the behavior of the C library function localtime().
这意味着如果您没有定义时区，服务器将尝试通过检查 C 库函数 localtime() 的行为来确定操作系统的默认时区。
If timezone is not specified in postgresql.conf or as a server command-line option, the server attempts to use the value of the TZ environment variable as the default time zone.
如果时区未在 postgresql.conf 中指定或作为服务器命令行选项指定，则服务器尝试使用 TZ 环境变量的值作为默认时区。
It seems to have the System's timezone to be set is possible indeed.
似乎确实可以设置系统的时区。
Get the OS local time zone from the shell. In psql:
从 shell 获取操作系统本地时区。在 psql 中：
=> \! date +%Z
回答by Sachin R
You can access the timezone by the following script:
您可以通过以下脚本访问时区：
SELECT * FROM pg_timezone_names WHERE name = current_setting('TIMEZONE');
- current_setting('TIMEZONE') will give you Continent / Capital information of settings
- pg_timezone_names The view pg_timezone_names provides a list of time zone names that are recognized by SET TIMEZONE, along with their associated abbreviations, UTC offsets, and daylight-savings status.
- name column in a view (pg_timezone_names) is time zone name.
- current_setting('TIMEZONE') 会给你设置的大陆/首都信息
- pg_timezone_names 视图 pg_timezone_names 提供了 SET TIMEZONE 识别的时区名称列表，以及它们相关的缩写、UTC 偏移量和夏令时状态。
- 视图中的名称列 (pg_timezone_names) 是时区名称。
output will be :
输出将是：
name- Europe/Berlin,
abbrev - CET,
utc_offset- 01:00:00,
is_dst- false
