5 ms·

> Storing UTC values in a timestamp without time zone column is, unfortunately, a practice commonly inherited from other databases that lack usable timezone sup

by drran 5y ago

> Storing UTC values in a timestamp without time zone column is, unfortunately, a practice commonly inherited from other databases that lack usable timezone support. WTF? Timestamp is not a user-facing value, so it doesn't need to have user time zone. Timestamps are always in UTC.

pc86 5y ago
To second the sibling comment, "UTC" doesn't appear in the article at all. "timezone" occurs in code samples only. "timestamp" appears exactly once in this sentence: > Let’s say we define a python function `convert_to_eastern` that takes a timestamp and converts it to the eastern time zone. It seems unlikely this 2-week old article was updated within the last 2 hours so I'm not sure which article you're quoting.
- drran 5y ago
  I clicked at wrong tab when responding, sorry.
atombender 5y ago
The sentence you quoted does not occur in the article, so I'm not sure about the context. But generally speaking, the problem is that an app might accidentally do: db.exec("INSERT INTO values (t) VALUES (?)", now()) and now your timezoneless column t probably contains the local time. A lot of code (including ORMs) assumes local time zones everywhere. It's simply hard to avoid, and too easy to accidentally insert bad data. But the above bug does not happen if your column is declared as something like "TIMESTAMP WITH TIME ZONE" (PostgreSQL, BigQuery, etc., or just "TIMESTAMP" in MySQL).
- drran 5y ago
  Sorry, I clicked on wrong tab, when responding. It was a sane choice, to use local timezone for dates, in the past, but it's no longer a sane choice in today's world, because a database can be at another continent, while users can be all around the world. «Timestamp» is somewhat different from «date». Timestamps can be represented by different dates in different time zones or different chronicle. «Local timestamps» with time zone are prone to error when time zone shifts.
colanderman 5y ago
Neither "TIMESTAMP WITH TIME ZONE" nor "TIMESTAMP WITHOUT TIME ZONE" stores a time zone. The difference is in the meaning of the value: a "TIMESTAMP WITH TIME ZONE" value represents an absolute time in history; "TIMESTAMP WITHOUT TIME ZONE" represents a time relative to some unspecified time zone. The types are so named because values of type "TIMESTAMP WITH TIME ZONE" are always displayed with a time zone (corresponding to the system's time zone) -- and the numeric value adjusted accordingly -- so the time they represent is unambiguous. Values of type "TIMESTAMP WITHOUT TIME ZONE" are displayed as-is without a time zone. The types behave differently when used with the "AT TIME ZONE" operator as well, which converts between the two. When presented with a "TIMESTAMP WITH TIME ZONE" value, "AT TIME ZONE" returns a "TIMESTAMP WITHOUT TIME ZONE" value which numerically is equal to the time in the specified time zone. When presented with a "TIMESTAMP WITHOUT TIME ZONE" value, "AT TIME ZONE" interprets the given time as relative to the indicated time zone, and returns a "TIMESTAMP WITH TIME ZONE" value representing that absolute point in time. Consequently, in cases where you would use UTC in other systems, you should use "TIMESTAMP WITH TIME ZONE" in PostgreSQL. In cases where you would use a local time in other systems, you should use "TIMESTAMP WITHOUT TIME ZONE" along with a "text" value naming the local time zone when appropriate.
- funcDropShadow 5y ago
  Unfortunately, my colleagues and me had the displeasure to look at that distinction between timestamp and timestamptz in Postgres very deeply. All that is written above is correct from my understanding, but it is not the complete picture. Even in the case of a column of type `TIMESTAMP WITHOUT TIME ZONE`, the Postgresql JDBC driver may convert time to UTC if your session time zone is not UTC. I write "may" because it depends on whether the binary protocol or the text base protocol is used and that depends on whether prepared statements are actually used between the Postgres client library and the server. We had the requirement to store dates and times coming it without changing them at all cost, but still using the space efficient epoch format. And we didn't know the time zone of the time when we had to store it.
  - colanderman 5y ago
    Ugh, sounds like a bug in the JDBC driver. I've found that type translation in some drivers is... incomplete let's say. I seem to recall working around similar issues in psycopg2/3(?). The PostgreSQL server itself though I've found to be very consistent handling these types.

by drran 5y ago

pc86 5y ago

To second the sibling comment, "UTC" doesn't appear in the article at all. "timezone" occurs in code samples only. "timestamp" appears exactly once in this sentence: > Let’s say we define a python function `convert_to_eastern` that takes a timestamp and converts it to the eastern time zone. It seems unlikely this 2-week old article was updated within the last 2 hours so I'm not sure which article you're quoting.

drran 5y ago
I clicked at wrong tab when responding, sorry.

atombender 5y ago

The sentence you quoted does not occur in the article, so I'm not sure about the context. But generally speaking, the problem is that an app might accidentally do: db.exec("INSERT INTO values (t) VALUES (?)", now()) and now your timezoneless column t probably contains the local time. A lot of code (including ORMs) assumes local time zones everywhere. It's simply hard to avoid, and too easy to accidentally insert bad data. But the above bug does not happen if your column is declared as something like "TIMESTAMP WITH TIME ZONE" (PostgreSQL, BigQuery, etc., or just "TIMESTAMP" in MySQL).

drran 5y ago
Sorry, I clicked on wrong tab, when responding. It was a sane choice, to use local timezone for dates, in the past, but it's no longer a sane choice in today's world, because a database can be at another continent, while users can be all around the world. «Timestamp» is somewhat different from «date». Timestamps can be represented by different dates in different time zones or different chronicle. «Local timestamps» with time zone are prone to error when time zone shifts.

colanderman 5y ago

Neither "TIMESTAMP WITH TIME ZONE" nor "TIMESTAMP WITHOUT TIME ZONE" stores a time zone. The difference is in the meaning of the value: a "TIMESTAMP WITH TIME ZONE" value represents an absolute time in history; "TIMESTAMP WITHOUT TIME ZONE" represents a time relative to some unspecified time zone. The types are so named because values of type "TIMESTAMP WITH TIME ZONE" are always displayed with a time zone (corresponding to the system's time zone) -- and the numeric value adjusted accordingly -- so the time they represent is unambiguous. Values of type "TIMESTAMP WITHOUT TIME ZONE" are displayed as-is without a time zone. The types behave differently when used with the "AT TIME ZONE" operator as well, which converts between the two. When presented with a "TIMESTAMP WITH TIME ZONE" value, "AT TIME ZONE" returns a "TIMESTAMP WITHOUT TIME ZONE" value which numerically is equal to the time in the specified time zone. When presented with a "TIMESTAMP WITHOUT TIME ZONE" value, "AT TIME ZONE" interprets the given time as relative to the indicated time zone, and returns a "TIMESTAMP WITH TIME ZONE" value representing that absolute point in time. Consequently, in cases where you would use UTC in other systems, you should use "TIMESTAMP WITH TIME ZONE" in PostgreSQL. In cases where you would use a local time in other systems, you should use "TIMESTAMP WITHOUT TIME ZONE" along with a "text" value naming the local time zone when appropriate.

funcDropShadow 5y ago
Unfortunately, my colleagues and me had the displeasure to look at that distinction between timestamp and timestamptz in Postgres very deeply. All that is written above is correct from my understanding, but it is not the complete picture. Even in the case of a column of type `TIMESTAMP WITHOUT TIME ZONE`, the Postgresql JDBC driver may convert time to UTC if your session time zone is not UTC. I write "may" because it depends on whether the binary protocol or the text base protocol is used and that depends on whether prepared statements are actually used between the Postgres client library and the server. We had the requirement to store dates and times coming it without changing them at all cost, but still using the space efficient epoch format. And we didn't know the time zone of the time when we had to store it.
- colanderman 5y ago
  Ugh, sounds like a bug in the JDBC driver. I've found that type translation in some drivers is... incomplete let's say. I seem to recall working around similar issues in psycopg2/3(?). The PostgreSQL server itself though I've found to be very consistent handling these types.