#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2017
    Location
    New Zealand
    Posts
    3
    Rep Power
    0

    Time zone conversions in Postgres


    I have a date time string of the form "2015-12-27T14:16:32Z" in UTC format and I wish to change the time zone value to NZ within
    a postgres stored procedure. I have a snippet of sql being used

    Code:
      --  P_timestamp is 2015-12-27T14:16:32Z
    
    insert into misc (log) values ('start');
        SELECT split_part(p_timestamp,'T',1) INTO v_date_val ;
    insert into misc (log) values (v_date_val);
    
        SELECT replace(split_part(p_timestamp,'T',2),'Z','') INTO v_time_val ;
    insert into misc (log) values (v_time_val);
    
        v_utc_datetime := v_date_val || ' ' || v_time_val || '-00' ;
    
    insert into misc (log) values (v_utc_datetime);
    
    --  SELECT ((timestamp v_utc_datetime) AT TIME ZONE 'UTC') AT TIME ZONE 'NZDT' 
        SELECT timestamp with time zone v_utc_datetime AT TIME ZONE 'NZ'  
    --  SELECT v_utc_datetime AT TIME ZONE 'NZ'
    I am logging to a table called misc so the logged data looks like this

    Code:
     start
     2016-12-21
     18:27:40
     2016-12-21 18:27:40-00
    In the last three lines of the code above I have been trying to convert the date time value to an NZ time zone
    value without success. ( I am using postgres 9.4.5 ). The errors that I am getting when I attempt each of those
    three time zone conversions are

    Code:
    select 1 error
    
    ERROR:  syntax error at or near "v_utc_datetime"
    LINE 40:     SELECT ((timestamp v_utc_datetime) AT TIME ZONE 'UTC') A...
    
    select 2 error
    
    ERROR:  syntax error at or near "v_utc_datetime"
    LINE 41:     SELECT timestamp with time zone v_utc_datetime AT TIME Z...
    
    select 3 error
    
    ERROR:  syntax error at or near "v_utc_datetime"
    LINE 40:     SELECT ((timestamp v_utc_datetime) AT TIME ZONE 'UTC') A..

    I wonder could someone advise me / tell me how to change then time zone ? cheers
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2017
    Location
    New Zealand
    Posts
    3
    Rep Power
    0
    I just wanted to say that I solved this problem by using the following approach


    SELECT v_utc_datetime::timestamp at time zone 'UTC'
    at time zone 'Pacific/Auckland'
    INTO v_nz_time ;
    So the variable v_utc_datetime contains a utc date time in the format YYYY-MM-DD HH:mm:SS. I want to change the time zone to
    Pacific Auckland. The variable is a string so I have cast it to a timestamp. When I get the result I want a string in the same format
    for further processing. i.e. I didnt want a date time string with an added time zone shift i.. "+13".

IMN logo majestic logo threadwatch logo seochat tools logo