Converting GMT to CST in postgresql

0
votes

I am currently working with raw data that have timestamps in GMT and I want to convert them to CST. I am trying to use the cast function to change the timestamp, but it is not working- the times are not affected. Most of what I have read about timezones in postgresql assumes that the default timezone is UTC so I'm not sure if there is a different syntax needed for when the data I'm trying to convert is GMT. Any help is greatly appreciated!

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"
2
postgresqltimestamptimezone
What is the type of the column that the timestamps are being stored in? What do you get when you select c."JoinDate" from "public"."flight_export_consultant" without any casting. FYI, Postgres does not assume the default timezone is UTC. Also for practical purposes GMT = UTC. – Adrian Klaver
@AdrianKlaver I get dates that look like September 24, 2018, 4:01PM – ekeckeisen
What is the type of field/column that the timestamp is stored in? – Adrian Klaver
Try this query to see if it helps you sort it out in your head: select "JoinDate", "JoinDate" at time zone 'UTC', "JoinDate" at time zone 'us/chicago' from flight_export_consultant where "JoinDate" is not null limit 10; – Mike Organek
@AdrianKlaver it says it is timestamp without time zone – ekeckeisen

2 Answers

0
votes

Given that JoinDate is type timestamp, this should be a good workaround for your situation now that we have established that the values in JoinDate of type timestamp represent UTC/GMT timestamps, and your server is not in UTC/GMT. The ideal solution is to use timestamptz columns.

The trick here is to cast JoinDate to text, append a z to it to make it UTC, and then cast it to timestamptz. Once that is done, you can use at time zone 'us/chicago' to do the conversion for you.

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";
2
votes

As @Mike Organek pointed out a field of type timestamp assumes local time on entry. So first thing you need to establish is where the dates are being entered from and whether they are are actually being entered as GMT. For the moment assuming they are you could do the following:

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

Basically you are 'anchoring' the timestamp at UTC/GMT and then converting to 'america/chicago'. In other words replicating what a timestamptz field does.