Re: to_timestamp() and timestamp without time zone

On Sat, Jun 25, 2011 at 3:56 AM, David Johnston <polobo(at)yahoo(dot)com> wrote:

> 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)"
That's also how the word is used in APIs (see Joda time)
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.

> 1) **“TimestampTZ” is an “INSTANT” - but what exactly is an Instant?

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.

I suggest to take a look at the Joda time API, which is one of the very few
date-time API ("key concepts") that is generally though to cover quite
completely and consistently these issues.
--
Hernán J. González
http://hjg.com.ar/