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.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
