timestamptz - store the same value without converting to system TZ

Hi,

Try using AT TIMEZONE
AT TIMEZONE

Hope this helps
NC

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.

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

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