时区感知date_trunc函数

Question

以下查询

SELECT the_date FROM date_trunc('day', timestamp with time zone 
       '2001-01-1 00:00:00+0100') as the_date

结果

the_date
2000-12-31 00:00

是否有一种方法告诉date_trunc做日/月/年转换基于它提供的时区?

预期输出为:2001-01-1 00:00+0100

Answer 1

您需要指定在哪个时区显示

select
    date_trunc(
        'day',
        timestamp with time zone '2001-01-1 00:00:00+0100' at time zone '-02'
    ) as the_date;
      the_date       
---------------------
 2001-01-01 00:00:00

AT TIME ZONE

Answer 2

虽然标记的答案对于OP的奇怪情况可能是正确的，但对于其他情况更可能是不正确的。您需要将date_trunc返回的时间戳转换为合适的时区。

select
    date_trunc(
        'day',
        some_timestamp at time zone users_timezone
    ) at time zone users_timezone as the_date;

要理解的重要事情是date_trunc返回没有附加时区的timestamp。您需要将时间戳转换为正确的时区，因为数据库客户端或任何下游可能具有不同的时区。

Answer 3

@Adam的回答肯定更有帮助。虽然我认为我们可以再次改进它，因为如果我们将时间戳截断为一天(或一周/一个月/等)，那么我们要确保我们处理的是一个日期对象，而不是一个时间戳。否则，我们可能会给其他代码段留下这样的印象:实际上发生了在午夜(或者可能是一天中的其他一些误导性的时间)。

所以我会用:

SELECT date_trunc('day', some_timestamp AT TIME ZONE users_timezone)::date AS the_date;

将结果强制转换为日期，而不是时间戳。

结果类似于:

  the_date
------------
 2019-09-14

而不是更容易误导人的结果:

      the_date
---------------------
 2019-09-14 00:00:00