PostgresSQL：将时间间隔添加到不同时区的时间戳
如果我不想在服务器的时区中进行计算，那么将指定时间间隔添加到带时区的时间戳中的最佳方法是什么？ 这对于日光节约转换尤为重要。
例如考虑我们“向前发展”的晚上。 （在多伦多，我认为这是2016年3月13日凌晨2点）。
如果我拿了时间戳： 2016-03-13 00:00:00-05并在加拿大/东部添加'1 day' ，我预计会得到2016-03-14 00:00:00-04 - > 1天后，但实际上只有23小时但是如果我在萨斯喀彻温省（一个不使用DST的地方）增加1天，我希望它增加24小时，以便在2016-03-13 01:00:00-04结束2016-03-13 01:00:00-04 。
如果我有列/变量
t1 timestamp with time zone;
t2 timestamp with time zone;
step interval;
zoneid text; --represents the time zone
我基本上想说
t2 = t1 + step; --in a time zone of my choosing
Postgres文档似乎表明带时区的时间戳在内部以UTC时间存储，这似乎表明timestamptz列没有时区的推算。 SQL标准指示日期时间+间隔操作应该维护第一个操作数的时区。
t2 = (t1 AT TIME ZONE zoneid + step) AT TIME ZONE zoneid;
似乎不工作，因为第一次将t1转换为无时区时间戳，因此无法计算DST转换
t2 = t1 + step;
似乎没有工作，因为它在我的SQL服务器的时区中的操作
在操作之前设置postgres时区，然后将其更改回来？
更好的例子：
CREATE TABLE timestamps (t1 timestamp with time zone, timelocation text);
SET Timezone 'America/Toronto';
INSERT INTO timestamps(t1, timelocation) VALUES('2016-03-13 00:00:00 America/Toronto', 'America/Toronto');
INSERT INTO timestamps(t1, timelocation) VALUES('2016-03-13 00:00:00 America/Regina', 'America/Regina');
SELECT t1, timelocation FROM timestamps; -- shows times formatted in Toronto time. OK
"2016-03-13 00:00:00-05";"America/Toronto"
"2016-03-13 01:00:00-05";"America/Regina"
SELECT t1 + '1 day', timelocation FROM timestamps; -- Toronto timestamp has advanced by 23 hours. OK. Regina time stamp has also advanced by 23 hours. NOT OK.
"2016-03-14 00:00:00-04";"America/Toronto"
"2016-03-14 01:00:00-04";"America/Regina"
如何解决这个问题？
a）在适当的时区将时间戳记转换为时间戳记tz？
SELECT t1 AT TIME ZONE timelocation + '1 day', timelocation FROM timestamps; --OK. Though my results are timestamps without time zone now.
"2016-03-14 00:00:00";"America/Toronto"
"2016-03-14 00:00:00";"America/Regina"
SELECT t1 AT TIME ZONE timelocation + '4 hours', timelocation FROM timestamps; -- NOT OK. I want the Toronto time to be 5am
"2016-03-13 04:00:00";"America/Toronto"
"2016-03-13 04:00:00";"America/Regina"
b）更改postgres的时区并继续。
SET TIMEZONE = 'America/Regina';
SELECT t1 + '1 day', timelocation FROM timestamps; -- Now the Regina time stamp is correct, but toronto time stamp is incorrect (should be 22:00-06)
"2016-03-13 23:00:00-06";"America/Toronto"
"2016-03-14 00:00:00-06";"America/Regina"
SET TIMEZONE = 'America/Toronto';
SELECT t1 + '1 day', timelocation FROM timestamps; -- toronto is correct, regina is not, as before
"2016-03-14 00:00:00-04";"America/Toronto"
"2016-03-14 01:00:00-04";"America/Regina"
如果我在每次操作时间间隔操作之前不断切换postgres时区，此解决方案才会起作用。
它是导致问题的两个属性的组合：
timestamp with time zone UTC格式存储，不包含任何时区信息。 一个更好的名字是“UTC timestamp”。
timestamp with time zone和interval的timestamp with time zone总是在当前时区中执行，即使用配置参数TimeZone设置的时TimeZone 。
由于您真正需要存储的是时间戳和时间戳，因此您应该存储timestamp without time zone的timestamp without time zone和表示时区的text的组合。
正如您正确注意到的那样，您必须切换当前时区才能正确执行夏令时间隔的时间间隔（否则PostgreSQL不知道1 day有多长时间）。 但是你不必亲自去做，你可以使用PL / pgSQL函数为你做这件事：
CREATE OR REPLACE FUNCTION add_in_timezone(
ts timestamp without time zone,
tz text,
delta interval
) RETURNS timestamp without time zone
LANGUAGE plpgsql IMMUTABLE AS
$$DECLARE
result timestamp without time zone;
oldtz text := current_setting('TimeZone');
BEGIN
PERFORM set_config('TimeZone', tz, true);
result := (ts AT TIME ZONE tz) + delta;
PERFORM set_config('TimeZone', oldtz, true);
RETURN result;
END;$$;
这会给你以下的结果，在同一时区作为参数被理解：
test=> SELECT add_in_timezone('2016-03-13 00:00:00', 'America/Toronto', '1 day');
add_in_timezone
---------------------
2016-03-14 00:00:00
(1 row)
test=> SELECT add_in_timezone('2016-03-13 00:00:00', 'America/Regina', '1 day');
add_in_timezone
---------------------
2016-03-14 00:00:00
(1 row)
test=> SELECT add_in_timezone('2016-03-13 00:00:00', 'America/Toronto', '4 hours');
add_in_timezone
---------------------
2016-03-13 05:00:00
(1 row)
test=> SELECT add_in_timezone('2016-03-13 00:00:00', 'America/Regina', '4 hours');
add_in_timezone
---------------------
2016-03-13 04:00:00
(1 row)
你可以考虑创建一个组合类型
CREATE TYPE timestampattz AS (
ts timestamp without time zone,
zone text
);
并定义运算符并对其进行强制转换，但这可能是一个超出您想要的项目的主要项目。
甚至还有一个PostgreSQL扩展timestampandtz， 也许这正是你需要的（我没有看到添加的语义是什么）。
链接地址: http://www.djcxy.com/p/59015.html上一篇: PostgresSQL: Adding an interval to a timestamp in a different time zone