1

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?

2

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:

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:

2
  • Thanks for your answer. However, I tried copy-pasting your code, but I still got 2016-02-20 15:15:54.000495+01
    – NumberFour
    Feb 23 '16 at 15:20
  • @NumberFour: That's how it's supposed to be - as opposed to "this query gives me 2016-02-20 14:15:54.000495+01" in your question. This might help to understand: stackoverflow.com/a/9576170/939860 Feb 23 '16 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.