2

Given the following data:

CREATE TABLE tbl (
    x timestamp, 
    y timestamptz
)

INSERT INTO tbl(x, y)
VALUES 
('2021-01-01'::timestamp, '2021-01-01'::timestamptz)
;

It's not obvious to me that the query:

SELECT * FROM tbl WHERE x <> y; 

Would return a value - given y contains timezone information and x doesn't.

I'm not sure if this is a general postgres comparison question, or if this is just in relation to timezones.

I thought that timestamptz contained additional information, so comparing it with a timestamp value would be false. But when thinking about what's actually being compared, I'm not sure, maybe there's a value somewhere else which is just rendered differently within the database (and they are equal), or perhaps postgres implicitly discards timezone information when comparing these values.

1 Answer 1

1

timestamp with time zone does not contain a time zone in PostgreSQL. The UTC offset is added when PostgreSQL displays the value as a string. The UTC offset shown will depend on the current setting of the parameter timezone, which every database session should set correctly.

If you compare a timestamp with time tone and a timestamp without time zone for equality, the UTC offset of the former will be ignored. So the result of the comparison depends on the current setting of timezone.

2
  • Thanks - so are the actual values stored on the DB unix seconds or something? And they are compared (hence equal), but when viewing the data in dbeaver / whatever it's formatted to look like a date with/without timezone info
    – baxx
    Commented Jan 5, 2023 at 11:07
  • The actual storage is in microseconds since 2000-01-01 midnight, where the meaning of "midnight" makes the difference between timestamp without time zone and timestamp with time zone. PostgreSQL doesn't compare these numerical values, it respects the setting of timezone. Commented Jan 6, 2023 at 6:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.