I’m trying to understand whether timestamp values are stored differently in PostgreSQL when using WITH TIME ZONE versus WITHOUT TIME ZONE. Can someone illustrate the differences with simple examples or test cases using Postgres timestamp with timezone?
Hey! I’ve run into this confusion before 
. In PostgreSQL:
timestamp without time zone just stores the date and time as-is. It doesn’t know your timezone.
timestamp with time zone (aka timestamptz) stores the instant in time internally in UTC and converts it to your session’s time zone when you query it.
Example:
-- Without timezone
CREATE TABLE ts_no_tz (t timestamp without time zone);
INSERT INTO ts_no_tz VALUES ('2025-10-06 12:00:00');
SELECT * FROM ts_no_tz;
-- Output: 2025-10-06 12:00:00
-- With timezone
CREATE TABLE ts_with_tz (t timestamptz);
INSERT INTO ts_with_tz VALUES ('2025-10-06 12:00:00+05');
SELECT * FROM ts_with_tz;
-- Output (in UTC): 2025-10-06 07:00:00+00
See the difference? timestamptz normalizes the time to UTC internally, while timestamp just stores whatever you type.
I often test with different time zones to understand behavior. Suppose my session is in UTC+2:
SET TIME ZONE 'UTC+2';
-- Insert into timestamptz
INSERT INTO ts_with_tz VALUES ('2025-10-06 12:00:00');
SELECT * FROM ts_with_tz;
-- Output: 2025-10-06 12:00:00+02 (internally stored as UTC 10:00)
With timestamptz, PostgreSQL automatically converts stored UTC time to your current session’s timezone.
With timestamp, the value stays exactly as stored, regardless of session timezone.
I usually follow this guideline:
Use timestamp without time zone if your app handles local times only and you never need to convert between time zones.
Use timestamptz if your app deals with global users, scheduling, or logging events, it avoids a lot of headaches with daylight saving and time zone conversions.