Re: to_timestamp() and timestamp without time zone

[hernan gonzalez <hgonzalez@xxxxxxxxx>]

On Sat, Jun 25, 2011 at 3:56 AM, David Johnston <polobo@xxxxxxxxx> 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/