CODEWITHSUNDEEP
postgresqldatedatetime
Mike
Mike

Reputation: 582

PostgreSQL "date at time zone" unexpected behaviour

The behavior of the at time zone operator on the date type is not as expected in PostgreSQL.

set time zone 'UTC';
select
  '2021-01-03'::date at time zone 'America/Chicago' as "value",
  pg_typeof('2021-01-03'::date at time zone 'America/Chicago') as "type";
x value type
Expected 2021-01-03 06:00:00+00 timestamp with time zone
Actual 2021-01-02 18:00:00 timestamp without time zone

DB Fiddle

Tested on PostgreSQL server versions 10, 11, 12, and 13.

This behavior is very strange to me, and I have not been able to find anywhere it is described or discussed. It seems PSQL is implicitly casting date to timestamptz (using the system time zone) and then applying the at time zone operator. I know that the at time zone operator is only defined (in the docs) for the timestamp(tz) and time(tz) types. However, I would have assumed PSQL would implicitly cast date to timestamp and not timestamptz because:

  1. It is a "safe" conversion (no need to assume a time zone). Simply append 00:00:00.
  2. It results in a timestamptz which one would expect when using the at time zone operator on a type that does not have a time zone.

The current behavior simply doesn't make sense to me and I can't think of a use case that would benefit from it. You are asking for the timestamp with time zone for a date at a particular time zone and instead you get a timestamp of the date at the system time zone localized to the given time zone.

Of course, a simple workaround is to cast to timestamp first:

set time zone 'UTC';
select '2021-01-03'::date::timestamp at time zone 'America/Chicago';

I was hoping someone could shed some light on this. What am I missing? Perhaps some overarching rule about how date is implicitly cast to timestamp(tz)? Is this behavior documented somewhere?

Thanks!

Upvotes: 0

Views: 2776

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247950

You ask "why". The reason is that AT TIME ZONE does not operate on date, so PostgreSQL invokes an implicit type cast to convert it to a different data type. Lacking an explicit directive, PostgreSQL opts for the preferred data type for datetime, which happens to be timestamp with time zone.

The data type resolution rules for operators are documented here, and the preferred type for a type category can be found in the typispreferred and typcategory columns of the pg_type system catalog.

Upvotes: 1

Sandeep
Sandeep

Reputation: 148

As per the Postgresql documentation: The AT TIME ZONE converts time stamp without time zone to/from time stamp with time zone, and time values to different time zones

Here in first query, you are trying to apply at time zone to date type, not to time stamp

set time zone 'UTC';
select
  '2021-01-03'::date at time zone 'America/Chicago' as "value",
  pg_typeof('2021-01-03'::date at time zone 'America/Chicago') as "type";

which is eventually gets ignored, and you will get a timestamp without timezone

But in second query,

set time zone 'UTC';
select '2021-01-03'::date::timestamp at time zone 'America/Chicago';

You are casting the date to time stamp first and then converting it to desired time zone, which gives you the expected result of time stamp with time zone.

Hope this clears your doubt.

Upvotes: 0

Related Questions