timestamptz - store the same value without converting to system TZ

Navneet_1

Hi,
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_C

Hi,

Try using AT TIMEZONE
AT TIMEZONE

Hope this helps
NC

Navneet_1

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_C

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

Navneet_1

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