Time zone conversion in PostgreSQL without timezone does the opposite operation

Question

Query:

SELECT TO_TIMESTAMP('2021-10-24 20:00', 'YYYY-MM-DD hh24:mi') as d,
 (TO_TIMESTAMP('2021-10-24 20:00', 'YYYY-MM-DD hh24:mi') at time zone 'utc')::timestamptz at time zone 'ASIA/CALCUTTA' as ist_w_tz,
 (TO_TIMESTAMP('2021-10-24 20:00', 'YYYY-MM-DD hh24:mi') at time zone 'utc')::timestamp at time zone 'ASIA/CALCUTTA' as ist_wo_tz

Result:

d: 2021年10月24日 20:00:00+00
ist_w_tz: 2021年10月25日 01:30:00 -- Correct
ist_wo_tz: 2021年10月24日 14:30:00+00 -- Opposite, its -5:30 rather than +5:30

Why does this happen? In the docs (section 8.5.1.3), it is specified that timestamp without timezone is considered to be in the local timezone. Shouldn't then the conversion without timezone be like

1. 2021年10月24日 20:00 without tz so assumes it to be local i.e IST so converts it to UTC
2. 20:00 - 05:30 = 14:30
3. Then to IST 14:30 + 05:30 = 20:00

Ran this using pgadmin4, select version() returns

PostgreSQL 13.4 (Debian 13.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

• jjanes: "In the docs (section 8.5.1.3), it is specified that timestamp without timezone is considered to be in the local timezone." The docs don't say that. Please quote the wording you are interpreting that way.

Answer 1

The main confusion seems to be that ...

• ... your 2nd expression ist_w_tz is type timestamp without time zone.
• ... your 3rd expression ist_wo_tz is type timestamp with time zone.

While your column names imply the opposite.

The AT TIME ZONE construct translates timestamptz to timestamp and vice versa, that's by design. The manual:

timestamp without time zone AT TIME ZONE zone → timestamp with time zone

Converts given time stamp without time zone to time stamp with time zone, assuming the given value is in the named time zone.

timestamp '2001-02-16 20:38:40' at time zone 'America/Denver' → 2001年02月17日 03:38:40+00

And:

timestamp with time zone AT TIME ZONE zone → timestamp without time zone

Converts given time stamp with time zone to time stamp without time zone, as the time would appear in that zone.

timestamp with time zone '2001-02-16 20:38:40-05' at time zone 'America/Denver' → 2001年02月16日 18:38:40

See:

• Ignoring time zones altogether in Rails and PostgreSQL

Also, you state:

it is specified that timestamp without timezone is considered to be in the local timezone.

I wouldn't know of such a specification. As the name implies, timestamp without time zone isn't considered to be in any time zone. It's just an abstract datetime value without time zone information. It's typically interpreted to be in the local time zone, but that's not inherent to the type or its values. To place it in a time zone, use the AT TIME ZONE construct.

Step-by-step breakdown

.. of your misleadingly named columns 2 and 3:

SELECT (to_timestamp('2021-10-24 20:00', 'YYYY-MM-DD hh24:mi') AT TIME ZONE 'UTC')::timestamptz AT TIME ZONE 'ASIA/CALCUTTA' AS ist_w_tz
 , (to_timestamp('2021-10-24 20:00', 'YYYY-MM-DD hh24:mi') AT TIME ZONE 'UTC') AT TIME ZONE 'ASIA/CALCUTTA' AS ist_wo_tz

1. (to_timestamp('2021-10-24 20:00', 'YYYY-MM-DD hh24:mi') .. produces timestamptz. That's what the function to_timestamp() does. (It might better be named to_timestamptz(), but that's beyond the scope of this question.)
   The current timezone setting is assumed for lack of input.
   Since your input literal is in ISO format, '2021-10-24 20:00'::timestamptz would do the same, simpler.

2. AT TIME ZONE 'UTC') ... produces the corresponding timestamp at the time zone UTC.

Now ...

3. ::timestamptz ... places that timestamp in your local time zone again (which typically makes no sense!).
4. AT TIME ZONE 'ASIA/CALCUTTA' ... produces the corresponding timestamp at the given time zone.

But ...

3. ::timestamp ... does nothing at all (the source already being type timestamp.
4. AT TIME ZONE 'ASIA/CALCUTTA' ... adds the given time zone to produce timestamptz.