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

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…)