在postgresql中将GMT转换为CST

Question

我目前正在处理GMT中有时间戳的原始数据，我希望将它们转换为CST。我试图使用cast功能来更改时间戳，但它不起作用-时间不受影响。我在postgresql中所读到的大多数关于时区的文章都假设默认时区是UTC，所以当我试图转换的数据是GMT时，我不确定是否需要一种不同的语法。任何帮助都是非常感谢的！

WITH RECURSIVE "child" AS (
    SELECT "ConsultantDisplayID",
           "JoinDate",
           "ParentPersonDisplayID"
    FROM "public"."flight_export_consultant"
    WHERE "ConsultantDisplayID" = '4019'
UNION 
    SELECT c."ConsultantDisplayID", 
           CAST(c."JoinDate" at time zone 'america/chicago' as timestamp) as "JoinDate"
           c."ParentPersonDisplayID"
    FROM "public"."flight_export_consultant" AS c 
    JOIN "child" AS cd 
    ON c."ParentPersonDisplayID" = cd."ConsultantDisplayID"),
    
"sponsor" AS (
    SELECT 
        "child".*,
        c1."ConsultantDisplayID",
        Cast(c."JoinDate" at time zone 'america/chicago' as timestamp) as "Sponsor JoinDate"
    FROM "public"."flight_export_consultant" AS c1
    LEFT JOIN "child" 
    ON c1."ConsultantDisplayID" = "child"."ParentPersonDisplayID")  

SELECT * FROM "sponsor"

Answer 1

既然JoinDate是timestamp类型，那么对于您的情况来说，这应该是一个很好的解决方案，因为我们已经确定，timestamp类型的JoinDate值表示UTC/GMT时间戳，而您的服务器不在UTC/GMT中。理想的解决方案是使用timestamptz列。

这里的诀窍是将JoinDate转换为text，在其上附加一个z以使其成为UTC，然后将其转换为timestamptz。一旦完成，您可以使用at time zone 'us/chicago'为您进行转换。

WITH RECURSIVE "child" AS (
    SELECT "ConsultantDisplayID",
           "JoinDate",
           "ParentPersonDisplayID"
    FROM "public"."flight_export_consultant"
    WHERE "ConsultantDisplayID" = '4019'
UNION 
    SELECT c."ConsultantDisplayID", 
           "JoinDate",
           c."ParentPersonDisplayID"
    FROM "public"."flight_export_consultant" AS c 
    JOIN "child" AS cd 
    ON c."ParentPersonDisplayID" = cd."ConsultantDisplayID"),
    
"sponsor" AS (
    SELECT 
        "child".*,
        c1."ConsultantDisplayID",
        c."JoinDate" as "Sponsor JoinDate"
    FROM "public"."flight_export_consultant" AS c1
    LEFT JOIN "child" 
    ON c1."ConsultantDisplayID" = "child"."ParentPersonDisplayID")  

SELECT "ConsultantDisplayID", 
       ("JoinDate"::text||'z')::timestamptz at 'america/chicago' as "JoinDate",
       "ParentPersonDisplayID",
       "ConsultantDisplayID",
       ("JoinDate"::text||'z')::timestamptz at 'america/chicago' as "Sponsor JoinDate"
FROM "sponsor";

Answer 2

正如@Mike指出的那样，timestamp类型的字段在输入时假定为本地时间。因此，您需要确定的第一件事是日期是从哪里输入的，以及它们是否实际上是作为GMT输入的。目前，假设他们是，你可以做以下工作：

select 'September 24, 2018, 4:01PM'::timestamp at time zone 'utc' at time zone 'america/chicago';
      timezone       
---------------------
 09/24/2018 11:01:00

-- Or if you want to stick to GMT

select 'September 24, 2018, 4:01PM'::timestamp at time zone 'gmt' at time zone 'america/chicago';
      timezone       
---------------------
 09/24/2018 11:01:00

基本上，您是在UTC/GMT‘锚定’时间戳，然后转换为'america/chicago'。换句话说，复制timestamptz字段的功能。