How is a timestamp with timezone read in PostgreSQL?

Question

Lets say I have a timestamp with timezone stored in my PostgreSQL database. Something like this:

2003-04-12 04:05:06.814191 America/New_York

When reading this data, would the database return the adjusted time in GMT to standardize all timestamps read, or would it simply return it as is?

The reason I ask this question is I created a table with records, where each record had a timestamp column. I went on to provide different timezones for different records. When I queried this table, and tried ordering by the timestamp column, the timezone did not seem to have any effect on the ordering. Is this by design?

A sample of my results:

1954-06-27 08:44:01.963454 Asia/Baghdad
1954-06-27 08:44:01.963454 GMT
1954-06-27 08:44:01.963454 Europe/Paris
1954-06-27 08:44:01.963454 Asia/Baghdad
1954-06-27 08:44:01.963454 America/New_York
1954-06-27 08:44:01.963454 America/New_York
1954-06-27 08:44:01.963454 America/New_York
1954-06-27 08:44:01.963454 GMT
1954-06-27 08:44:01.963454 America/New_York
1954-06-27 08:44:01.963454 America/New_York
1954-06-27 08:44:01.963454 Europe/Paris
1954-06-27 08:44:01.963454 America/New_York
1954-06-27 08:44:01.963454 Asia/Dhaka
1954-06-27 08:44:01.963454 GMT
1954-06-27 08:44:01.963454 GMT
1954-06-27 08:44:01.963454 Asia/Damascus
1954-06-27 08:44:01.963454 GMT
1954-06-27 08:44:01.963454 Asia/Damascus
1954-06-27 08:44:01.963454 America/New_York
1954-06-27 08:44:01.963454 Asia/Dhaka
1954-06-27 08:44:01.963454 Asia/Baghdad
1954-06-27 08:44:01.963454 Europe/Paris
1954-06-27 08:44:01.963454 Europe/Paris
1954-06-27 08:44:01.963454 Asia/Baghdad
1954-06-27 08:44:01.963454 Asia/Baghdad

The date and time are the same for all these records, and the varying timezones had no effect on the ordering.

To make it more clear, I am actually storing these timestamps as strings in a JSONb column called data with a key of datetime, ie:

data = {
    "datetime" : "2003-04-12 04:05:06.814191 America/New_York",
    .....
}

During ordering and filtering, I'm casting it as a timestamp. Something like this:

"(data->>'datetime')::timestamp"

No timezone data is associated with the TIMEZONETZ type—its' just an instant in time. Can you show how you're inserting/selecting your example data? — teppic
@teppic : Sorry I forgot to mention an important detail earlier. Updated the question. — darkhorse
If you're casting to timestamp, that means timestamp without time zone, not timestamp with time zone. — Wyzard

Wyzard Wyzard · Accepted Answer · 2019-01-02T00:01:05

PostgreSQL's "timestamp with time zone" type does not actually store a time zone offset in each record. It just normalizes everything to UTC internally.

The point of "timestamp with time zone" is that because the stored value is represented in a known time zone, it unambiguously represents a specific point in time. The alternative, "timestamp without time zone", is ambiguous because it has a time but doesn't say what zone it's in — the record might say noon today, but is that noon in Greenwich? Noon in New York? Noon in Tokyo? The "with time zone" type avoids that problem, because the zone is always Greenwich.

(For comparison: I believe Oracle has a "timestamp with time zone" that actually stores an offset in each record, and a "timestamp with local time zone" that normalizes everything to the server's time zone to avoid having to store per-record offsets. PostgreSQL's "timestamp with time zone" is named like the former, but behaves like the latter.)

However, it sounds like you're not actually storing a "timestamp with time zone" in your table; you're storing essentially a string, and casting to timestamp as part of a select. The timestamp keyword by itself refers to the "without time zone" variant (as required by the SQL standard), and the PostgreSQL documentation says:

In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication. That is, the resulting value is derived from the date/time fields in the input value, and is not adjusted for time zone.

I'd expect that the same applies with casting, though if that's the case it's not quite clear why your output (of timestamp values) includes the TZ names. But you probably want to cast to timestamptz instead, or maybe create a separate timestamptz column (so the values can be indexed).

The PostgreSQL documentation on date/time types is a good reference.

How is a timestamp with timezone read in PostgreSQL?

1 Answers