postgresqltimestamptimestamp-with-timezone

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

Solution

  • 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'