服务器时区偏移值 [英] Server timezone offset value
问题描述
我正在使用相对于数据库服务器的日期。
I am working with dates relative to the database server.
在Oracle中，要获取完整的时区偏移量，可以使用以下内容：
In Oracle, to get the full timezone offset you would use something like the following:
select tz_offset(SESSIONTIMEZONE) from dual;
或
select tz_offset(DBTIMEZONE) from dual;
在我的情况下，这些将返回（重要的是包括符号）
These would return, in my case (importantly it includes the sign)
-04:00
我想知道Postgres中存在什么功能来获得与上述Oracle版本完全相同的格式的结果？我需要知道服务器的时区偏移量，包括表示它位于GMT / UTC之前或之后的符号。
I was wondering what functionality exists in Postgres to get a result of the exact same format as the Oracle version above? I need to know the timezone offset of the server inclusive of the sign indicating it being behind or ahead of GMT/UTC.
推荐答案
对于时区，您可以：
SHOW timezone;
或等价货币：
SELECT current_setting('TIMEZONE');
但这可以是服务器接受的任何格式，因此它可能返回 UTC ， 08:00 ，澳大利亚/维多利亚或类似名称。
but this can be in any format accepted by the server, so it may return UTC, 08:00, Australia/Victoria, or similar.
令人沮丧的是，似乎没有内置的功能可以报告客户端使用的以小时和分钟为单位的UTC时间偏移，这对我来说似乎有些疯狂。您可以通过将UTC的当前时间与本地的当前时间进行比较来获得偏移量：
Frustratingly, there appears to be no built-in function to report the time offset from UTC the client is using in hours and minutes, which seems kind of insane to me. You can get the offset by comparing the current time in UTC to the current time locally:
SELECT age(current_timestamp AT TIME ZONE 'UTC', current_timestamp)`
...但是IMO可以更容易地提取秒数的tz偏移量 current_timestamp 并转换为间隔：
... but IMO it's cleaner to extract the tz offset in seconds from the current_timestamp and convert to an interval:
SELECT to_char(extract(timezone from current_timestamp) * INTERVAL '1' second, 'FMHH24:MM');
除了不会产生前导零外，它将与所需结果匹配，因此 -05：00 只是 -5：00 。令人讨厌的是，似乎不可能让 to_char 产生数小时的前导零，这给我留下了以下难看的手动格式：
That'll match the desired result except that it doesn't produce a leading zero, so -05:00 is just -5:00. Annoyingly it seems to be impossible to get to_char to produce a leading zero for hours, leaving me with the following ugly manual formatting:
CREATE OR REPLACE FUNCTION oracle_style_tz() RETURNS text AS $$
SELECT to_char(extract(timezone_hour FROM current_timestamp),'FM00')||':'||
to_char(extract(timezone_minute FROM current_timestamp),'FM00');
$$ LANGUAGE 'SQL' STABLE;
在 timezone_hour 和 timezone_minute 而不是我之前使用的黑客工具，其中包括 extract（current_timestamp中的时区）* INTERVAL'1'秒）和CTE。
Credit to Glenn for timezone_hour and timezone_minute instead of the hack I used earlier with extract(timezone from current_timestamp) * INTERVAL '1' second) and a CTE.
如果不需要前导零，则可以使用：
If you don't need the leading zero you can instead use:
CREATE OR REPLACE FUNCTION oracle_style_tz() RETURNS text AS $$
SELECT to_char(extract(timezone from current_timestamp) * INTERVAL '1' second, 'FMHH24:MM');
$$ LANGUAGE 'SQL' STABLE;
另请参见：
- Local time zone offset in PostgreSQL
- How to know a timezone of a timestamp in postgresql 8.3
这篇关于服务器时区偏移值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
