Re: Some clarification about TIMESTAMP

On 05/31/2011 12:00 PM, hernan gonzalez wrote:
>> There are any number of
>> server-side settings that can affect the interpretation (and display)
>> of your data. Datestyle for example already renders this position
>> untenable.
>
> What makes me a little uncomfortable in this assertion -and in many
> parts of PG docs-
> is that emphasis put on what "is displayed", as assuming that I will
> be using postgresql
> in the server, and using the command-line psql. But of course one
> 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.
> (I suspect, from some peeking at the source I did once, that
> internally the "canonical"
> representation of values in memory is as a string, the same that it's
> displayed/parsed
> in the psql - and so, even if I don't use the psql CLI, the convertion
> to/from string happens
> the same internally - is this true? that's far from obvious for me.
> Say: when I query a timestamp from the DB via JDBC to display in a jsp page, it
> first converted from the binary store format to a psql-like string,
> then the jdbc driver
> parses that string to convert it to a Date java object, and the
> finally Java converts
> it to a string again? Is that so?)

The string representation is generally how dates and times are passed
from one program to another. It is the lowest common denominator and
most programs have routines to convert the string into their own
internal representation.

>> It did, but extract(epoch) assumes you want a distance from the real
>> Unix epoch, so it takes the timestamp as being in local zone. AFAIR
>> there isn't a function that does exactly what you seem to be thinking
>> of.
>
> 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.

The important part to understand is that a timestamp without timestamp
values are unanchored in time. There are assumptions that come into play
when the database works with them and we all know what assumptions do:)
If you want to anchor a timestamp value to a particular point in time
you need to use timestamp with timezone.

>
> Regards
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com