Re: to_timestamp() and timestamp without time zone - Mailing list pgsql-general

On Sunday, June 26, 2011 12:57:15 pm hernan gonzalez wrote:

>

> An instant is a point in the universal time, it's a physical concept,

> unrelated to world calendars. The time point at which the man first landed

> on the moon is an instant, as is the moment at which my server restarted.

> It is not related to a Timezone at all. We can specified it by some

> arbitrary convention (milliseconds passed since the first atomic explosion

> at Hiroshima), or by some human calendar at some place/moment: for

> example, the "wall date and clock used at New York". If (only if) you use

> a Gregorian Calendar to specify/show a instant, you need a date, a time

> and a timezone. (but you have many timezones to choose from - as you have

> several calendars - a timezone is not determined by an instant). A full

> datetime (date, time, timezone) implies an instant - but an instant does

> not imply a timezone.

While I agree with the relativity comment I would offer that you do have a solid (or at least practically accepted) understanding of date/time but seem to lack the ability to describe exactly what it is that the PostgreSQL modeling of date/time lacks.  Using you “physical” definition for “Instant” what can you not do with a PostgreSQL “timestamptz” (or is much harder than you would like) that you would like to do?

I wholly agree that TimeZones are an “arbitrary convention” that are not “required” in order to describe time.  However, they are in use by humans and ultimately the databases we create are “models” and thus those models reflect human conventions.  Thus, it really does not matter, in the context of PostgreSQL, whether you can or even should describe an “Instant” without using a TimeZone.  You have already said that a “TimeStampTZ” should represent an “Instant” and we have shown that it indeed does so.

Also, generally, the concept of “TimeZone” is not restricted only to a “Gregorian Calendar” and in fact could be used with any calendar.  With respect to PostgreSQL currently you are correct.  But now you’ve mixed “physical” concept assertions and “conventional” concept assertions into the same paragraph…

In PostgreSQL:  An instant is represented by a “timestamptz” which by definition uses a TimeZone and thus “Instant”, in PostgreSQL, implies that there IS “TimeZone” (not which one, since, as you said, the choice of TimeZone is arbitrary on output).  However, on input, this implication means that there HAS TO BE an in-effect TimeZone when interpreting and storing an “Instant”.  Since you did not specify that you were dealing with an “Abstract” Instant in your original post we presume, this being a PostgreSQL list, that your definitions where meant to be taken in context of PostgreSQL.

Back to my original point; you seem to have something positive to contribute but for whatever reason you are failing to communicate in a way that we can understand.  If it is because you are focusing on some theoretically perfect model of date/time I would suggest come down off the theory and put things into more practical terms.   Focus less on definitions and more on properties and behaviors.  If you cannot do that, giving up (and maybe coming back to it later) is probably a good idea.  But don’t let a one-liner scare you off.  Take it as a sign that you probably need to change your approach.  Or, in this specific case, your abrupt introduction of “physical concept” 10 posts into the thread provoked an off-hand remark kind of like: “why didn’t you say you wanted to discuss theory – your bashing of the timestamptz data type made us think you actually wanted to deal with something practical”.

FWIW

David J.