这应该很简单,但我遇到了一个尴尬的麻烦。
在 PostgreSQL 9.1 中,我需要将一个作为DATE
存储在数据库中的字段进行构造,就好像它是一个表示该日期午夜 UTC 的TIMESTAMPTZ
。我想以一种干净易读的方式做到这一点,有人可以来,看看,理解正在发生的事情。
到目前为止,我发现的唯一方法都是非常丑陋的。将其转换为TIMESTAMP WITHOUT TIME ZONE
然后通过将其解释为UTC来创建TIMESTAMP WITH TIME ZONE
:
SELECT CAST(DATE '2012-01-01' AS TIMESTAMP WITHOUT TIME ZONE) AT TIME ZONE 'utc'
另一种方式更糟:
('2012-01-01'::date)::timestamptz - (current_timestamp AT TIME ZONE 'UTC' - current_timestamp)
因为它将日期转换为当地时间午夜的时间戳,然后减去时区偏移量。我找不到任何方法将偏移量作为本地间隔(这似乎很疯狂(,所以我通过比较本地时间的current_timestamp
与 UTC 的current_timestamp
来获得它。
我唯一能计算出的另一种方法是extract
获取日期部分并从中组装新timestamptz
。我什至不会展示那个,它太丑了。
这两种方法都让人感觉各种奇怪和错误。是否有任何理智的方法 - 标准或否 - 以可读且易于理解的方式从该日期的DATE
转换为午夜UTC timestamptz
?
我正在寻找类似的东西(虚构的,行不通(
'2012-01-01'::date AS TIMESTAMPTZ IN TIME ZONE '00:00';
或
to_timestamp('2012-01-01'::date, '00:00'::time, 'UTC');
请指出我错过的愚蠢明显的东西。
请注意,我正在测试以确保日期在内部确实正确,而不仅仅是在显示时,extract(epoch from $1)
其中$1
是转换后的日期。
你的第一种方法是正确的。而且它并没有那么丑,是吗?在简化的 Postgres 语法中:
SELECT '2012-1-1'::date::timestamp AT TIME ZONE 'UTC';
应用于变量或列,它看起来更加优雅:
SELECT mydate::timestamp AT TIME ZONE 'UTC';
如果要手动输入日期,可以快捷方式:
SELECT '2012-1-1 0:0'::timestamp AT TIME ZONE 'UTC'
结果将始终根据客户端的本地时区显示(即具有相应的偏移量(,但这对值没有影响。
注意:我对 PSQL 了解不多,但我对日期/时间问题有一些经验。
你的第一种方式对我来说是正确的。您实际上是从"本地日期"到"本地日期/时间"再到"特定时区的日期/时间"。这些都是合理的步骤,我希望在正常的日期/时间API中看到这些步骤。
据我所知,这种方法从未引入系统默认时区,这是一件非常好的事情。它一次执行一个逻辑步骤,假设投射是明智的。
您无需担心本地日期/时间在目标时区中不明确或缺失,因为 UTC 没有任何 DST 转换。
基本上,它看起来很好。如果它像你需要的那样工作和表现,我会坚持下去。