Postgres:获取本地时间戳和最近的午夜时区

Question

我在时区欧洲/柏林(+02)，Postgresql运行在UTC(+00)。我需要在最晚的本地午夜日期(我的时区中当前一天的开始日期)获得时区的时间戳。

所以我的最终结果将是这样的如果我们有2013-03-03 14:00:00+02

2013-03-03 22:00:00+00
2013-03-04 00:00:00+02 // the same

我试着用

得到这个日期

SELECT TIMESTAMP 'today' AT TIME ZONE 'Europe/Berlin'

不幸的是，这会在00:00和02:00之间产生错误的日期(前一天的午夜)，因为UTC时间仍然在前一天，今天似乎使用UTC来计算其余的时间。

如果我们在欧洲/柏林有2013-03-03 00:05，这将返回

2013-05-01 22:00:00+00

如果我想要得到正确的日期，我需要使用

SELECT date_trunc('day', now() AT TIME ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin';
2013-05-02 22:00:00+00

是正确的，但是很难看。

该命令是否有更简洁的变体?

Answer 1

使用timestamptz。最后的tz含义with time zone:

SELECT TIMESTAMPTZ 'today' AT TIME ZONE 'Europe/Berlin'

或者如果你喜欢更明确的:

SELECT TIMESTAMP with time zone 'today' AT TIME ZONE 'Europe/Berlin'

Answer 2

将其封装在函数中:

create function midnight() returns timestamptz as $$
  select date_trunc('day', now() AT TIME ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin';
$$ language sql;

Answer 3

根据Erwin对一个相关问题的回答，这是我想出的最简单、最快的方法:

SELECT timezone('Europe/Berlin', now()::date::timestamp) AS local_midnight_in_utc;

键被转换为时间戳，从日期中删除时区。

您可以这样测试'2013-03-03 00:05'的样本时间:

SELECT timezone('Europe/Berlin', '2013-03-03 00:05'::date::timestamp) AS midnight;

返回

2013-03-02 23:00:00+00

根据解释分析，这大约是datetrunc版本的3倍。运行时间为0.017 ms vs 0.006ms(基于最多5次运行)