Search code examples
postgresqltimezonetimestamp

Convert timestamp in UTC to timestamptz

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

Solution

  • Better:

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

    No additional cast after the AT TIME ZONE construct. It returns timestamp with time zone (= timestamptz) for timestamp without time zone (= 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. Text output (display) is adapted to the current time zone setting of your session. Only the bare value of the corresponding UTC time is stored. The time zone itself is never stored. If you need it, store it additionally in another column. In your particular case, UTC happens to be the time zone used for input as well.

    Detailed explanation: