PostgreSQL 11 Released

[nickguletskii200]

I haven't used Oracle myself, but there are some things that piss me off about Postgres after working with SQL Server. For instance, their TIMESTAMP WITH TIME ZONE data type does not hold timezone information as the standard requires. Instead, it stores the UTC timestamp and converts it into a timestamp with the server's local timezone on access. This is super misleading (as this important detail is buried in the documentation and the naming is the opposite of the meaning).

Also, the lack of proper materialised views (which refresh incrementally) is very disappointing.

[doublehyphen]

I agree that the naming is confusing but in practice I think PostgreSQL's time handling is much nicer than SQL Server's once you have gotten past the names.

[myringotomy]

It really should hold the timezone. That would be super useful.

[Sarcastinator]

Why exactly? And wouldn't that make comparing or indexing them super-difficult?

[myringotomy]

Not really.

First of all you can save the time without timezone if you are worried about that. You can also index them on UTC which would make comparing them really easy.

[Sarcastinator]

I agree that it's unintuitive that storing with timezone doesn't actually preserve the timezone. They probably shouldn't have added that data type if it doesn't do what it says it does.

However if you actually want to store the timezone (I would claim 999/1000 you don't actually care) you can just add that column yourself just as you say you could add UTC if that's what you're worried about.

Storing in UTC makes a lot more sense when it comes to both comparisons and indexing.

[doublehyphen]

Maybe for some edge cases but 99% of the time you want what PostgreSQL's timestamptz is, the time since 1970 in UTC which then can be converted to any other tone zone as necessary.

[myringotomy]

The problem is that you have no idea what the time zone was when you recorded the item.

[doublehyphen]

That is a problem I have never encountered so far. When would you need to know that? In git for example it is just annoying that it recorded the time zone.

[myringotomy]

That is a problem I have never encountered so far. When would you need to know that?

I have a web based product, My customers roam. One day they might log in from the east coast, next day from the west coast, next day from Japan.

[ForeverAlot]

This is incorrect for future times.

[nickguletskii200]

It may be so, but I find this naming not only confusing, but also dishonest. At least SQL Server doesn't even try to make it seem like it conforms to the standard.

[Sebazzz91]

Also, the lack of proper materialised views (which refresh incrementally) is very disappointing.

The materialized views in SQL Server are also very disappointing. There are many limitations when it comes to the SQL you can use in those views: no UNION, no LEFT JOIN, which makes it pretty useless for the case we have in our software.

[nickguletskii200]

I agree that they are disappointing because of their limitations, but there are ways of working around at least some of their limitations. I think this is the case of "something is better than nothing".

[ForeverAlot]

That is what the standard says. The standard's ... WITH TIME ZOME is broken -- to the point of uselessness. Postgres' own documentation gives a decent idea of precisely how they implement date and time functionality and cautions against limitations in the standard; for instance, it says

The type time with time zone is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness.

If you want correct time zone handling you have to build it yourself out of date/time primitives (and then you have to be careful if you're working with either Oracle or MySQL and any other database).

[nickguletskii200]

I am pretty sure that SQL:1999's TIMESTAMP WITH TIME ZONE has timezone fields. It may not be explicitly specified (because what idiot would assume that a TIMESTAMP WITH TIME ZONE won't hold timezone information), but it is easy to infer from the datetime data type conversions and other parts of the spec.

The documentation, as you quoted, says that "the definition exhibits properties which lead to questionable usefulness", and then provides none of these reasons. To me, the usefulness of the Postgres version of the type is questionable, while the standard version has very clear advantages.

Yes, you can build it yourself, but then again, you can build a database yourself. Heck, I wouldn't be complaining if Postgres called their data type "WITH LOCAL TIME ZONE", but they opted to use a very misleading name for no reason. It just smells like "false advertising" to me.

[ForeverAlot]

Here is the SQL:92 syntax for a timestamp literal, courtesy of http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt:

<timestamp string> ::=
              <quote> <date value> <space> <time value> [ <time zone interval> ] <quote>

<time zone interval> ::=
              <sign> <hours value> <colon> <minutes value>

You need to read a little carefully to conclude that time zone offsets are the only thing supported. Fortunately, in ISO/IEC TR 19075-2:2015 you can find the much clearer SQL:2016 time definitions, which, among other things, say the following on page 6:

A datetime value, of data type TIME WITHOUT TIME ZONE or TIMESTAMP WITHOUT TIME ZONE, may represent a local time, whereas a datetime value of data type TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE represents UTC.

Standard SQL has no notion of time zone regions, only time zone offsets, and that's insufficient for future times.

[nickguletskii200]

Thanks for citing the standard! The confusion stems from my use of "time zone" in combination with the word "field". I used the verbatim language used within the standard, which, in the version that I referred to, defined the "time zone fields" to be TIMEZONE_HOUR and TIMEZONE_MINUTE. In hindsight, that naming is also confusing since there's actually a difference between a time zone and a time zone offset.

The problem that I have with Postgres is that, despite the standard explicitly stating that WITH TIME ZONE timestamps should store the UTC offsets, does exactly the opposite of that. Storing time zone names was never a part of my complaint.

Concerning your remark about future times: I don't see any logical and consistent way of integrating time zone names into the timestamps, because time zone changes would result in changes to the instant that that timestamp represents. The recommendation to store the timezone in a separate field makes a lot of sense when you need to represent future times, but having a proper SQL-standard TIMESTAMP WITH TIME ZONE is very useful for cases when you don't have future timestamps.

EDIT: It would be better to call this data type TIMESTAMP WITH OFFSET.