On Thu, Feb 26, 2009 at 10:50:18AM +0100, Daniel Verite wrote:
> Is there a format string for to_char(timestamptz, text) that would
> output a timestamp in full ISO-8601 format? That is, something like
> 1977-04-22T01:00:00-05:00
If I'm understanding correctly, that's a bit awkward to do. PG only
ever records timestamps as an instant in time and throws away any
timezone information as soon as it can (i.e. as soon as it's been
parsed). The difference between a timestamp with a timezone and without
is as follows:
1) values of type "timestamp with time zone" are always converted
to UTC (either using the timezone specified or using the session's
current "timezone" value) and then when they're sent back to the value
is then corrected to the session's timezone (or an explicit AT TIME
ZONE can be specified).
2) values of type "timestamp without time zone" (or alternatively
spelled just "timestamp") ignore any timezone specified and assume the
value is UTC.
I find it all quite confusing (and hence the above may be wrong) but
have the luxury of ignoring it as all as my users are in the same
timezone. That said, it's a useful set of abstractions as lots of
people use them regularly.
I'd recommend a read through the docs at:
http://www.postgresql.org/docs/current/static/datatype-datetime.html
--
Sam http://samason.me.uk/