Convert timestamp in UTC to timestamptz

Question

How do I convert a timestamp that is in UTC to timestamptz?

If my local time zone is GMT-1 and I run:

select '2017-01-01 00:00:00'::timestamptz

I get:

2017-01-01 00:00:00-01

but I want:

2017-01-01 01:00:00-01

Answer 1

Better:

SELECT timestamp '2017-01-01 00:00:00' AT TIME ZONE 'UTC';

All you need is the AT TIME ZONE construct, no additional cast after that. It returns timestamptz for timestamp input and vice versa.

For the given example, the shortest, most efficient way to provide a timestamp constant is timestamp '2017-01-01'. Or use a cast, almost as good: '2017-01-01'::timestamp. The time component 00:00:00 is assumed when missing.

There is no such thing as a "timestamp that is in UTC". A timestamp carries no time zone information. Only you know that it's supposed to be located in the UTC time zone.

The type name timestamp with time zone is a bit misleading. timestamptz does not carry any time zone information, either. The given time zone name, abbreviation or offset is used to compute the corresponding UTC time. The standard display is adapted to the current time zone setting of your session. But only the bare value of the corresponding UTC time is stored. The time zone itself is never stored. If you need it, you have to store it explicitly in another column. In your particular case, UTC happens to be the time zone used for input as well.

Detailed explanation:

• Time zone storage in data type "timestamp with time zone"

• Ignoring time zones altogether in Rails and PostgreSQL

Answer 2

Solution: select ('2017-01-01 00:00:00' at time zone 'utc')::timestamptz