Question 418869 in Debian Question posted in
The oficial documentation can be found here.

Debian – Why pg_typeof(TIMESTAMP '2004-10-19 10:23:54+02') shows timestamp without time zone

From docs https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT-TIME-STAMPS

TIMESTAMP ‘2004-10-19 10:23:54+02’
is a timestamp with time zone

Question: Why select pg_typeof(TIMESTAMP '2004-10-19 10:23:54+02'); gives timestamp without time zone? Expecting timestamp with time zone as docs say.

Postgres version

SELECT version(); shows
PostgreSQL 14.14 (Debian 14.14-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

Tags:

2

Answers


  1. 0

    As it says in that very documentation you link to:

    PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both of the above as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type:

    TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
    Login or Signup to reply.
  2. 0

    timestamp means timestampwithOUTtime zone. That’s the type you used explicitly and that’s what pg_typeof() just repeated after after you.

    timestampTZ means timestampwithtime zone. That’s what you want and that’s the real pg_types.typname.
    demo at db<>fiddle

    select pg_typeof(timestampTZ '2004-10-19 10:23:54+02');
select pg_typeof(timestampTZ('2004-10-19 10:23:54+02'));
select pg_typeof('2004-10-19 10:23:54+02'::timestampTZ);
select pg_typeof(make_timestampTZ(2004,10,19,10,23,52,'+02'));
select pg_typeof(to_timestamp(1098174234));--no TZ in the name but it returns timestampTZ

    Postgres can always clarify things like that if you ask it to explain verbose:

    explain verbose select timestamp '2004-10-19 10:23:54+02';
    QUERY PLAN
    Result (cost=0.00..0.01 rows=1 width=8)
    Output: ‘2004-10-19 10:23:54’::timestamp without time zone
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search