PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Why is string literal '2021-02-16 09:00' without timezone information interpreted as `timestamp with time zone` with AT TIME ZONE?

set timezone = 'Asia/Ho_Chi_Minh';

I set times displayed to GMT+7.

select '2021-02-16 09:00' AT TIME ZONE 'Asia/Singapore';
select '2021-02-16 09:00+06' AT TIME ZONE 'Asia/Singapore';

Output

2021-02-16 10:00:00
2021-02-16 11:00:00

Because AT TIME ZONE converts timestamp without time zone to timestamp with time zone and vice versa (https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT), and because both outputs are timestamp without time zone (you can see they don’t have + – part, or pg_typeof to confirm), i infer that both string literals are interpreted as timestamp with time zone.

I understand the 2nd select has the +06 part to indicate time zone which makes sense.

Question:

Why does the 1st select which has no + or – time zone part get interpreted as timestamp with time zone like the 2nd select?

I know I can specify type with TIMESTAMP/TIMESTAMP WITH TIME ZONE or cast with ::timestamp/::timestamptz, but assuming i don’t do any of that, is there any docs describing what’s going on to the string literal?

Tags:

2

Answers


  1. 0

    The string literal in select '2021-02-16 09:00' AT TIME ZONE 'Asia/Singapore'; (i.e., the part '2021-02-16 09:00'), gets interpreted as having a timezone because you set the time zone to 'Asia/Ho_Chi_Minh'.

    PostgreSQL treats '2021-02-16 09:00' as if it were 2021-02-16 09:00 in the Asia/Ho_Chi_Minh time zone (UTC+7).

    Therefore all times are associated with being in Ho Chi Minh. As you mentioned you’ll need to use SELECT '2021-02-16 09:00:00'::timestamp AT TIME ZONE 'Asia/Singapore'; and it will be treated as a timestamp without time zone at first. If you had never set the time zone to Ho Chi Minh, PostgreSQL will use the default time zone (in my case, America/New York).

    Login or Signup to reply.
  2. 0

    It’s because of type preference. For at time zone construct to work, the unknown literal you specified needs to be either timestamp or timestampTZ – to break the tie between these and pick one, Postgres checks pg_type.typispreferred:

    typispreferred bool

    True if the type is a preferred cast target within its typcategory.

    TimestampTZ is the preferred type in that group:
    demo at db<>fiddle

    select typname
      ,typcategory 
      ,typispreferred 
from pg_type 
where typname ilike 'timestamp%';
    typname typcategory typispreferred
    timestamp D means "Date/time types" f
    timestamptz D T

    Postgres rewrites at time zone as a pg_catalog.timezone() function call. It’s overloaded and one of the variants available accepts timestampTZ as the second param. Unless you explicitly assign a type to the literal and pick timestamp, Postgres has to guess and pick one for you, following the predefined preference of timestampTZ.
    This means in both your examples you’re starting with a timestampTZ, which is then stripped by at time zone, returning a shifted timestamp without time zone.

    As a side note, it should be your preferred type, too.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search