Skip site navigation (1) Skip section navigation (2)

Re: to_timestamp() and timestamp without time zone

From: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Cc: hernan gonzalez <hgonzalez(at)gmail(dot)com>
Subject: Re: to_timestamp() and timestamp without time zone
Date: 2011-06-26 20:42:54
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
On Sunday, June 26, 2011 12:57:15 pm hernan gonzalez wrote:

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

Took you advice and looked up the Joda API definition of an instant:

"The most frequently used concept in Joda-Time is that of the instant. An 
Instant is defined as an instant in the datetime continuum specified as a number 
of milliseconds from 1970-01-01T00:00Z. This definition of milliseconds is 
consistent with that of the JDK in Date or Calendar. Interoperating between the 
two APIs is thus simple. "

Look a lot like the Unix Epoch:)

"Within Joda-Time an instant is represented by the ReadableInstant interface. 
There are four implementations of the interface provided: 
Instant - A simple immutable implementation which is restricted to the UTC time 
zone and is intended for time zone and calendar neutral data transfer
DateTime - The most commonly used class in the library, and an immutable 
representation of a date and time with calendar and time zone
DateMidnight - Similar to DateTime and also immutable but with the time 
component forced to be midnight (at the start of a day)
MutableDateTime - A mutable representation of date and time with calendar and 
time zone
We recommend the immutable implementations for general usage. "

There are those pesky time zones and calendars again.

Adrian Klaver

In response to


pgsql-general by date

Next:From: Adrian KlaverDate: 2011-06-26 20:50:17
Subject: Re: to_timestamp() and timestamp without time zone
Previous:From: hernan gonzalezDate: 2011-06-26 20:36:32
Subject: Re: to_timestamp() and timestamp without time zone

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group