Re: Some clarification about TIMESTAMP

On Tue, May 31, 2011 at 04:00:21PM -0300, hernan gonzalez wrote:

> frequently (mostly?)
> access the DB remotely and from a client interface (eg. JDBC), one
> would say that the
> display/interpret (from to a string) ocurrs normally in an upper
> layer, not in the DB.

In my experience, FWIW, handling dates in the upper layer is a good
way to introduce subtle inconsistencies as different programmers use
slightly different facilities in the date handling. I much prefer to
work on systems where the date arithmetic is done with Postgres's date
handling, and the result is just spit out to the application. This is
obviously not always possible. OTOH, a need to do a lot of date
manipulation up in the application can be -- not must, but can -- a
clue that you have something wrong with your transaction handling
model. I've often seen the strategy of putting all the business logic
out in the application result in this sort of date handling, for
instance, and that can often the the source of a large number of round
trips as well.

That said,

> Weel, it seems that if I want that timezone-agnostic behaviour,
> so that extract(epoch) always returns the same integer for a given stored
> value (and different server-configred timezones) I must use (no very intuitive)
> a TIMESTAMP WITH TIMEZONE.

. . .yes. Do everything in UTC, and then you have the best of all
worlds here.

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca