Re: to_timestamp() and timestamp without time zone

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.