PostgreSQL的timezone(zone, timestamp)函数是用来做什么的？

Question

我正在尝试为日期添加时区，但结果很奇怪。这是我的查询：

SELECT  timezone('GMT+5:30','2014-03-15'::date);

结果值为

"2014-03-14 13:00:00" (timestamp without timezone)

有人能解释一下为什么输入'2014-03-15'会返回"2014-03-14 13:00:00"吗？

我的时区是Asia/Calcutta(GMT+5:30)

- pgollangi

---

你的会话时区是什么？SHOW timezone; - vyegorov

会话时区是 "Asia/Calcutta" @vyegorov - pgollangi

Answer 1

您可能知道，PostgreSQL允许函数重载。
要检查所有名为timezone的可能函数，请运行此查询：

SELECT proname,pg_get_function_result(oid),pg_get_function_arguments(oid)
  FROM pg_catalog.pg_proc
 WHERE proname ~ '^timezone$';

正如您所看到的，它们都不接受“date”类型作为参数。实际上这是完全有效的，因为没有时间组件，时区是没有意义的。
因此，根据您提供的数据类型，您的输入被转换为带有时区的时间戳（基于结果数据类型），即2014-03-15 00:00:00+XX，其中XX取决于您的位置。

如果您位于格林威治标准时间之前，这将导致时间减法以便在所需区域返回时间戳。

此查询报告了什么：

SELECT current_timestamp, setting
  FROM pg_settings
 WHERE name = 'TimeZone';

---

更新

是的，这确实是棘手的问题。我强烈建议多次阅读此答案。

请按照以下步骤操作：

SET TimeZone TO 'Asia/Calcutta'; -- you don't have to do this
SELECT ts AT TIME ZONE 'UTC', ts AT TIME ZONE 'UTC' AT TIME ZONE 'UTC',
       row_number() OVER () rn
  FROM (VALUES (now()),
/* 2 */ ('2014-03-15'::date),
/* 3 */ ('2014-03-15'::date::timestamptz),
/* 4 */ ('2014-03-15'::date::timestamptz AT TIME ZONE 'UTC'),
/* 5 */ ('2014-03-15'::date::timestamptz AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'),
/* 6 */ ('2014-03-15'::timestamp),
/* 7 */ (timezone('GMT+5:30','2014-03-15')),
/* 8 */ (timezone('GMT+5:30','2014-03-15'::date)),
/* 9 */ (timezone('GMT+5:30','2014-03-15'::timestamp)),
/*10 */ (timezone('GMT+5:30','2014-03-15'::timestamp)  AT TIME ZONE 'UTC'),
/*11 */ (timezone('GMT+5:30','2014-03-15'::timestamptz)),
/*12 */ (timezone('GMT+5:30','2014-03-15'::timestamptz)  AT TIME ZONE 'UTC')) t(ts);

请检查输出结果。

您的情况对应于这里的第#4行。您在这里有2014-03-14 18:30:00，但由于您处于时区+05:30并且函数应返回timestamp without time zone，因此您得到了2014-03-14 13:00:00作为结果。

请在手册中找到更多关于这些类型的信息，并且还要检查AT TIME ZONE构造。

- vyegorov

---

由于我的时区是“亚洲/加尔各答”，即GMT+5:30，我应该得到“2014-10-14 18:30:00”（（2014-03-15 00:00:00）- 5:30）。但是我得到的是“2014-10-14 13:00:00”。 - pgollangi