Re: to_timestamp() and timestamp without time zone

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of hernan gonzalez
Sent: Sunday, June 26, 2011 3:57 PM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] to_timestamp() and timestamp without time zone

An analogy: a "localtime" is like a "relative path" in a filesystem.

A full datetime spefication (date, time, timezone) would correspond to a

absolute path.

So let us call it "Relative Time" instead of "Abstract/Local Time". I like
this better since the term relative begs the question: "relative to what"
which then needs to be answered (by adding a TimeZone) to possibly represent
an Instant (or absolute time). However there is no guarantee that the
process of turning the relative path into an absolute path will succeed just
as there is no guarantee that a relative time exists when associated with a
specific TimeZone.

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".

That's not the way in which the expression "Local (date) time" is normally
used,

rather the opossite.

A "localtime" is normally a time which is to be understood relatively to
some

unespecified timezone.

Precisely, when we (in common usage)

specify a datetime, we say a date, a time and then either add some

specific timezone OR do not state it : just say "localtime". That is, we
either say

"The event happened 2011-10-03 12:00:00 , Eastern Time"

OR we say

"The event happened 2011-10-03 12:00:00 (localtime)"

"International offices will close the first semester at 2011-06-31 23:59:00
(localtime)"

In all the above examples the use of the "localtime" simply means "in the
timezone in which the event physically happened or in which the post offices
are physically located. It is exactly in this sense that I suggest
LocalTime be used. The TimeZone itself may or may not be known but it is
implied and present none-the-less. This make sense because any time
specification of a physical event can and is specified by an instant. In
fact, in pretty all common usage we are dealing with Instances and not
"Relative Time". This is because we are dealing with the past and thus the
"location" is already known. It is when the location is unknown, in the
future or if there could be multiple, that you supply the Abstract Time to
the user and let them figure out at what Instant they should do something.
So, saying "go to bed at 2AM local time" means "bed time is 2AM no matter
where you are; when you arrive someplace attach your current TimeZone to
the 2AM Abstract Time and go to bed at that Instant.

That's also how the word is used in APIs (see Joda time)

The Joda-Time API indeed defines its "LocalTime" is the same manner as I've
suggested defining "Relative Time"; I would argue that their use of
"LocalTime" is also mis-guided for the reasons already stated. Saying that
someone else does something is generally a poor defense for a position.
Group-think is useful but, as in this case, your supporting group is
fallible; they made the same mistake as you (or rather your parroting of
their position reflected their mistake as well). It is a subtle and
inconsequential mistake since most people would make the same one and the
actual definitions and implementations are consistent and serve the needed
purpose. In the same way "timestamp" and "timestamptz" have their own
"mistakes" surrounding them (due to the re-characterization during the
version 7.x timeline) but they are consistently defined and implemented and
provide the same two models that Joda-Time does - just under different
labels. One sensible paraphrase of "LocalTime" - to include the prefix
"local" - is "A time value that needs to be localized in order to possibly
represent an instant." This works but for this, as for other things
generally, I first determine what it is I need to represent and then I scan
the documentation for Names that seems to do similar things. I then read
the description so see whether I have found the correct one. Naming
perfection is not required; as long as the label is close in meaning I'll
generally find what is needed if it is there. Then I just associate that
API/label with the behavior/feature I need and use it simply as-is - without
an in-depth analysis of whether it is fully logical since - in programming -
it is difficult to fix public "mistakes".

David J.