Hacker News Reader

Comment by jeremyevans | Hacker News Reader

> I'd just like to point out that a lot of RDBMSs support storing date and time alongside timezone information directly without using two separate fields. SQL Server has datetimeoffset, PostgreSQL has timestamp with time zone, and Oracle has timestamp with time zone.

PostgreSQL's "timestamp with time zone" doesn't store timezone, it converts the time to UTC and stores that, and on retrieval converts the value to the connection's time zone.

View on HN

Part of the problem is the ANSI SQL standard specifies that 'TIMESTAMP' does not have any offset/timezone applied. The postgres docs pretty much admit this is a bad idea and recommend all timestamps be stored as 'TIMESTAMPTZ' (aka TIMESTAMP WITH TIME ZONE).

It's problematic when moving data between systems with different Locale settings. Because ANSI timestamps are stored as 'local' time, timestamps will shift if you read from a DB in New York and write to one in San Francisco. Both will interpret an ANSI SQL timestamp as being in their locale unless told otherwise.

Yeah, that's true. That's closer to Oracle's TIMESTAMP WITH LOCAL TIME ZONE data type. In PostgreSQL you have to use the AT TIME ZONE to override the output time zone, so the client has to know which time zone they want. And I'm sure there are weird corner cases where information is lost.

That sounds dumb (if column type is called like that text in the quotes)

It is, and it is.

SQL data types have some verbose names, like "NATIONAL CHARACTER VARYING (20)". PG adds a "TIMESTAMPTZ" as alias for "TIMESTAMP WITH TIME ZONE" (which I'm not sure whether is a sql ansi standard type or just convention).

Why? If you're doing things correctly, you don't need to specify data types particularly often in SQL. You specify it once when the table is created and that's it. All you care about on the programming interface is that your application knows what data type of your language to use with each data column. What's wrong with a verbose and descriptive name for a data type?

I don't think they're saying that having verbose name is dumb, I think it's that a type called "timestamp with time zone" doesn't actually store a time zone.

Yeah, that's what I initially thought, too, but some of the other responses have made me question that.

Might sound "dumb" or confusing (which it is), but makes a lot of sense. I've written a post explaining the difference between "timestamp with/without time zone": http://phili.pe/posts/timestamps-and-time-zones-in-postgresq...

Reply on news.ycombinator.com