timestamptz - store the same value without converting to system TZ

I am trying to store a value into a Timestamptz datatype. 
While retrieving the value, I see that it gets converted to the TZ of DB. Same is not true with timetz datatype. 
Is there a way to retain the exact same value as is in input while using timestamptz ?
I understand that in essence the value is same, but I want to retain the value in same TZ as was in input.
=> select ('2014-05-10 07:00:37.327348-07:00' :: timestamptz(6)) ;         
Output::  2014-05-10 09:00:37.327348-05


  • Navin_CNavin_C Vertica Customer

    Try using AT TIMEZONE

    Hope this helps
  • Thanks Navin, I already tried this. But it removes the TZ i.e. 07:00 part. I want to retain it and at the same time without altering it.
  • Navin_CNavin_C Vertica Customer

    Not sure, if this is the requirement.
    dbadmin=> select ('2014-05-10 07:00:37.327348-07:00' :: timestamptz(6));           ?column?
     2014-05-10 10:00:37.327348-04
    (1 row)
    Time: First fetch (1 row): 8.263 ms. All rows formatted: 8.301 ms
    dbadmin=> select ('2014-05-10 07:00:37.327348-07:00' :: timestamptz(6)) at timezone 'US/Eastern' ;
     2014-05-10 10:00:37.327348
    (1 row)
    Time: First fetch (1 row): 8.625 ms. All rows formatted: 8.673 ms
    nnani=> select ('2014-05-10 07:00:37.327348-07:00' :: timestamptz(6)) at timezone 'US/Eastern' || '-' || right(cast(('2014-05-10 07:00:37.327348-07:00' :: timestamptz(6)) as varchar),2) ;
     2014-05-10 10:00:37.327348-04
    (1 row)
    Time: First fetch (1 row): 11.547 ms. All rows formatted: 11.593 ms
    Hope this helps
  • Thanks Navin, I hope I got you correctly, please correct me if wrong. 

    The intent is to capture exact same string as was in input and store it in TimestampTZ column.
    Having said that, 

    In the first method, as I mentioned earlier, it implicitly converts the TZ to local DB TZ. 
    In second, it trims the timezone/meridian. 

    and in third, we are converting it to a TZ of choice. And in any case, concatenating any coerced values will implicitly typecast it to Varchar and thus, rendering it unfit to be stored in a column of type TSTZ without explicitly typecasting it back to TSTZ. 

    So unfortunately, these methods don't seem to be fitting in.

    I am trying something with intervals, but that also is not helping.

    Also, TTZ does not have any such issue:
    select '2014-05-10 23:00:37.327348-07:00' :: date || ' ' ||  '2014-05-10 23:00:37.327348-07:00'  :: timetz(6) ;

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file