Re: to_timestamp() and timestamp without time zone

First: I would suggest your use of "Local Time" is incorrect and that you
would be better off thinking of it as "Abstract Time". My responses below
go into more detail but in short you obtain a "Local" time by "Localizing"
and "Abstract" time. The process of "Localization" requires a relevant
"Locale" input which, for date/time values, is a "TimeZone". Since you
define your "Local Time" as being "Without Timezone" this is an
inconsistency and so, because we want to define something without a TimeZone
we need to rename "Local Time" to "Abstract Time". And, no, "Wall Time"
will not work either since a "Wall" exists "Somewhere" and thus is
"Localized".

You say: (I am applying the above directly to your two definitions)

1) "TimestampTZ" is an "INSTANT" - but what exactly is an Instant? In
this case we have a "Wall Calendar" and a "Wall Clock" as a means of
describing an instant. However, that "Wall" has to be "Somewhere" and, in
combination, the calendar and clock have to display real and valid values
according to that physical location. So Instant, by definition, means
Local, which requires a TimeZone. So "TimeStampTZ" DOES imply a "TimeZone"
via a definition of "INSTANT".

2) "Timestamp" is a[n] [ABSTRACT]DATETIME (reworded to remove the
prefix LOCAL which, from above, I feel is misleading). That this does not
use TimeZone is correct.

On the other hand, "TIMESTAMP WITHOUT TIMEZONE" is a wholy different concept
(neither 'wider' or narrow' type than the other). It's just the local
calendar time, it's (conceptually) like a tuple of
numbers{year,month,day,hour,min,sec, fracsecs}, the "date and time" as it's
understood in the business-civil world, with the timezone information
missing.

This is the type that should be used for that concept, when it's critical
for me that 'If I stored "2011,09,11 at 23:59:59", I want to get that
precise calendar date.

Your definition of "calendar time" is incomplete (though I do get your
point). The date component is "local" because you have (implicitly)
specified that you are using a "Gregorian Calendar-like" rule set. However,
by omitting the "Location Time Rules (TimeZone)" you are in fact creating an
Abstract Time and not anything that is guaranteed to be valid (meaningful)
when "Localized". As soon as you say "local" you must tell the computer
what "local" means by specifying a TimeZone. Otherwise you simply have an
Abstract Time based on the (I think) Babylonian system.

Docs should make clear this, and the fact that Postgresql currently lacks a
"FULL" datetime type. This would correspond conceptually to the tuple
{INSTANT,TIMEZONE} (which implies univocally also a LOCALDATETIME). Nor does
it implemented the SQL standard "TIMESTAMP WITH TIMEZONE" (actually a
{INSTANT,OFFSET} tuple). I'm ok with that, I agree that the later is
crippled, and the former is complex and not standard, and that both have
little support from upper layers.

Fair enough; but in reality, other than the 25 hour day issue the chosen
implementation is quite useful. Once you have created a valid instance of a
"timestamptz" you can change it to any TimeZone, using the proper rules, and
are guaranteed to still have a valid value. So you might as well normalize
the "storage" TimeZone as PostgreSQL does. My only qualm is coercing the
input so that a valid "timestamptz" is always created. But even that isn't
a big deal if you indeed want to ensure that the value entered is a valid
"timestampz".

In this case you are using a function that returns a "timestamptz" while you
are working with "timestamp". You just said that they are completely
different so the fact that this fails should be of no surprise. That a
suitable function, that returns a "timestamp", does not exist is the only
real complaint. It has already been shown that the described behavior of a
PostgreSQL "timestamp" is consistent with what you describe it should be.
That it can be auto-casted to a "timestamptz" is a debatable point. But
this again comes simply back to the decision to coerce the input of
"timestamptz". That is, in the vast majority of cases where the conversion
makes sense the ability to directly cast is great. Casting is always
necessary IF you want to convert your Abstract Time (i.e., "timestamp") into
a Local Time (i.e., "timestamptz").

I'm ignoring the concept of "OFFSET" intentionally as that is likely to
confuse the issue and I haven't had time to fully contemplate that aspect of
things.

David J.

Note: I am writing this post and a response to Steve at the same time (no
pun intended.)