1

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

1
  • "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.
    – jjanes
    Commented Oct 25, 2021 at 14:21

1 Answer 1

3

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 zonetimestamp 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 zonetimestamp 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:

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 ...

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

But ...

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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