Skip site navigation (1) Skip section navigation (2)

Re: Format string for ISO-8601 date and time

From: Sam Mason <sam(at)samason(dot)me(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Format string for ISO-8601 date and time
Date: 2009-02-26 14:13:47
Message-ID: 20090226141347.GB32672@frubble.xen.chris-lamb.co.uk (view raw or flat)
Thread:
Lists: pgsql-general
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/

In response to

Responses

pgsql-general by date

Next:From: Nagalingam, KarthikeyanDate: 2009-02-26 15:10:47
Subject: Re: postgresql with storage
Previous:From: Scott MarloweDate: 2009-02-26 14:11:56
Subject: Re: Restore DB

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group