timestamptz - store the same value without converting to system TZ

Comments

• Navin_C Vertica Customer ✭

  August 2014

  Hi,
  
  Try using AT TIMEZONE
  AT TIMEZONE
  
  Hope this helps
  NC

  0
• Navneet_1

  August 2014

  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.

  0
• Navin_C Vertica Customer ✭

  August 2014

  Hi,
  
  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' ;
            timezone
  ----------------------------
   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) ;
             ?column?
  -------------------------------
   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
  NC

  0
• Navneet_1

  August 2014

  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) ;
  

  0