I saw this weird Postgres behaviour:

Running this query in Postgres:

select 
      '2025-02-20' AT TIME ZONE 'Asia/Kolkata' str_only,
      '2025-02-20'::date AT TIME ZONE 'Asia/Kolkata' cast_to_date,
      '2025-02-20'::timestamptz AT TIME ZONE 'Asia/Kolkata' cast_to_tz,
      '2025-02-20'::timestamp AT TIME ZONE 'Asia/Kolkata' cast_to_ts

Output comes out to be:

str_only	cast_to_date	cast_to_tz	cast_to_ts
2025-02-20 05:30:00	2025-02-20 05:30:00	2025-02-20 05:30:00	2025-02-19 18:30:00+00

I saw this weird Postgres behaviour:

Running this query in Postgres:

select 
      '2025-02-20' AT TIME ZONE 'Asia/Kolkata' str_only,
      '2025-02-20'::date AT TIME ZONE 'Asia/Kolkata' cast_to_date,
      '2025-02-20'::timestamptz AT TIME ZONE 'Asia/Kolkata' cast_to_tz,
      '2025-02-20'::timestamp AT TIME ZONE 'Asia/Kolkata' cast_to_ts

Output comes out to be:

str_only	cast_to_date	cast_to_tz	cast_to_ts
2025-02-20 05:30:00	2025-02-20 05:30:00	2025-02-20 05:30:00	2025-02-19 18:30:00+00

Why does adding ::timestamp cast decrease the time difference while others increase it. Take any time zone, adding ::timestamp does the opposite of what others do.

Unable to understand this behaviour. Does anyone know the explanation for this?

• postgresql
• datetime
• timestamp
• timezone

Share Improve this question Follow edited Mar 3 at 16:20 Erwin Brandstetter 661k158158 gold badges1.1k1.1k silver badges1.3k1.3k bronze badges asked Mar 3 at 14:49 Hemanth S. VaddiHemanth S. Vaddi 48322 gold badges99 silver badges2424 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 4

Fact 1: timestamptz is literally the "preferred" type among "Date/time types" in Postgres (set typeispreferred in pg_type).

Fact 2: timestamp and timestamptz literals include a time component. Time '00:00' is assumed when missing. The same happens when casting a date to one of these types.

Fact 3: A timestamptz literal includes a time offset. If absent, Postgres defaults to the timezone setting of the current session.

Fact 4: The AT TIME ZONE construct is applicable to both timestamp and timestamptz. It returns timestamp for timestamptz input and vice versa. So the input type determines what the construct does.

See:

• Is timestamptz preferred when timezone logic is performed by the client?
• Generating time series between two dates in PostgreSQL
• Ignoring time zones for Postgres timestamps

Consequently, the only expression in your test that I would use (in persisted code anyway) is expression 4 ("cast_to_ts"). Assuming you want to know the absolute point in time when it's '2025-02-20 00:00' in 'Asia/Kolkata', it's also the only correct one.

select '2025-02-20'::timestamp AT TIME ZONE 'Asia/Kolkata' AS cast_to_ts

The first 3 expressions suffer from introducing unnecessary points of failure (and also tripping over them). All three depend on the timezone setting of the current session and end up being wrong (if my above assumption is correct).

Expression 1

select '2025-02-20' AT TIME ZONE 'Asia/Kolkata' AS str_only

The AT TIME ZONE construct is applicable to both timestamp and timestamptz. The untyped literal '2025-02-20' is coerced to timestamptz due to fact 1. Since time and time offset are missing, '00:00' is assumed for the time, and the timezone setting of your current session is applied - obviously UTC. AT TIME ZONE converts this timestamptz value to type timestamp showing the local timestamp in 'Asia/Kolkata' when it's '2025-02-20 00:00' in UTC time zone.

Expression 2

select '2025-02-20'::date AT TIME ZONE 'Asia/Kolkata' AS cast_to_date

Casting the initial '2025-02-20' to date ends up doing exactly the same as expression 1. The preferred "timestamp" type is timestamptz, so the date is coerced to that initially. Then it takes the same route.

Expression 3

select '2025-02-20'::timestamptz AT TIME ZONE 'Asia/Kolkata' AS cast_to_tz

Takes the same route, effectively. Time and time offset are missing in the timestamptz literal, so '00:00 +0' is assumed ...