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