Re: Format string for ISO-8601 date and time

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/