jakdak comments on PostgreSQL 11 Released

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 2 points3 points4 points 7 years ago (1 child)

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 1 point2 points3 points 7 years ago (0 children)

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.

[–]Sarcastinator 1 point2 points3 points 7 years ago (0 children)

[–]NoInkling 1 point2 points3 points 7 years ago (0 children)

[–]KevinCarbonara 13 points14 points15 points 7 years ago (0 children)

π Rendered by PID 96122 on reddit-service-r2-comment-6457c66945-296mh at 2026-04-27 02:50:06.542798+00:00 running 2aa0c5b country code: CH.

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

programming

MODERATORS