存储/检索带时区或不带时区的时间戳

Question

我想测试时区和时区与时区的差异。但我遇到了一些我不明白为什么它会这样工作的东西。

我有以下设置：

CREATE TABLE tbl_test_timezones (
    first_date timestamp with time zone, 
    second_date timestamp 
) 

我已经插入了一些TESTDATA：

insert into tbl_test_timezones (first_date, second_date) values (now(), now()); 

现在我想检查这两个领域之间的差异时，我做了选择

select 
    first_date, 
    first_date at time zone 'Europe/Brussels' as first_date_wt, 
    second_date, 
    second_date at time zone 'Europe/Brussels' as second_date_wt 
from tbl_test_timezones 

which gives me the following result 

first_date:  2016-10-03 07:03:16.63818+00  
first_date_wt: 2016-10-03 09:03:16.63818 
second_date: 2016-10-03 07:03:16.63818 
second_date_wt: 2016-10-03 05:03:16.63818+00 

问题1

我想知道为什么second_date_wt正在做-2而不是+2像first_date_wt？

问题2

比方说，我的应用程序商店与时区的时间戳和用户想要得到一些记录这个时间戳。您是否需要为用户输入自己的时区来存储某种用户设置，并在检索时将其包含在选择查询中？或者你更喜欢获取它并在客户端应用程序中执行时区内容？

例如：

select start_date at time zone (
    select user_time_zone from tbl_user_settings where user_id = 2 
) 
from tbl_projects 

或只做

select start_date 
from tbl_projects 

Answer 1

回答问题1

这两个值有不同的含义。

first_date AT TIME ZONE 'Europe/Brussels' 

答案：在这个时间点布鲁塞尔的挂钟是什么？

second_date AT TIME ZONE 'Europe/Brussels' 

答案：布鲁塞尔的挂钟在什么时间显示这个值？

回答问题2

要做到这一点，最好的办法是由配置参数设置TimeZone到客户端应用程序的时区。然后，所有timestamp with time zone值将在这个时区显示，并timestamp without time zone值将在该时区被解释为价值观：

SHOW TimeZone; 

    TimeZone 
--------------- 
Europe/Vienna 
(1 row) 

SELECT 
    TIMESTAMP WITH TIME ZONE '2016-10-01 00:00:00 UTC' AS "midnight at UTC", 
    CAST(
     TIMESTAMP WITHOUT TIME ZONE '2016-10-01 00:00:00' 
     AS TIMESTAMP WITH TIME ZONE 
    ) AS "midnight local"; 

    midnight at UTC  |  midnight local 
------------------------+------------------------ 
2016-10-01 02:00:00+02 | 2016-10-01 00:00:00+02 
(1 row) 

SET TimeZone = 'America/Los_Angeles'; 

SELECT 
    TIMESTAMP WITH TIME ZONE '2016-10-01 00:00:00 UTC' AS "midnight at UTC", 
    CAST(
     TIMESTAMP WITHOUT TIME ZONE '2016-10-01 00:00:00' 
     AS TIMESTAMP WITH TIME ZONE 
    ) AS "midnight local"; 

    midnight at UTC  |  midnight local 
------------------------+------------------------ 
2016-09-30 17:00:00-07 | 2016-10-01 00:00:00-07 
(1 row)