Converting UTC time to timestamptz always adds the +01 offset in Postgres In Postgres 9.5, I do the following to convert a timestamptz to the UTC timezone:

SELECT TIMESTAMP WITH TIME ZONE '2016-02-20 15:15:54.000495+01' AT TIME ZONE 'UTC'


this outputs correctly 2016-02-20 14:15:54.000495 as timestamp without timezone.

I would like to store this value in a timestamptz column. However, when I do the following:

SELECT (TIMESTAMP WITH TIME ZONE '2016-02-20 15:15:54.000495+01' AT TIME ZONE 'UTC')::timestamptz


I would expect to get 2016-02-20 14:15:54.000495 as timestamptz.

However, this query gives me 2016-02-20 14:15:54.000495+01 as timestamptz, which sadly doesn't even represent the original time.

Whats the reason of such behavior? How do I make this work correctly?
A simple cast from timestamp to timestamptz assumes your current time zone. If you want the cast to assume UTC (or any other time zone) you have to use the AT TIME ZONE construct again:

SELECT (timestamptz '2016-02-20 15:15:54.000495+01' AT TIME ZONE 'UTC') AT TIME ZONE 'UTC';


You may be missing that the AT TIME ZONE has two different use cases. It can transpose timestamptz to timestamp and vice versa.
Related answer with detailed explanation:


Confused about TIMESTAMPTZ internal conversion


Be aware that your statement does not make sense:


  I would expect to get 2016-02-20 14:15:54.000495 as timestamptz.


The text representation of timestamptz is always displayed as local time according to your current time zone setting, extended with an offset to UTC. The literal you display has no offset, so it is a timestamp literal, not timestamptz. Basics here:


Ignoring timezones altogether in Rails and PostgreSQL