Format string for ISO-8601 date and time

Hello

2009/2/26 Daniel Verite <daniel@manitou-mail.org>:

Hi,

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

I can't find a way to extract the offset against GMT from the docs here:
http://www.postgresql.org/docs/8.3/static/functions-formatting.html

If not, what would be the way to convert a timestamp to such a string
regardless of the session's datestyle settings?

try to look on function extract, there you can get timezone from any
timestamp with time zone.

regards
Pavel Stehule

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/

Pavel Stehule 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

I can't find a way to extract the offset against GMT from the docs

here:

http://www.postgresql.org/docs/8.3/static/functions-formatting.html

If not, what would be the way to convert a timestamp to such a

string

regardless of the session's datestyle settings?

try to look on function extract, there you can get timezone from any
timestamp with time zone.

Thanks, I've come up with this expression, then:

to_char(date, 'YYYY-MM-DD')
|| 'T'
|| to_char(date, 'HH24:MI:SS')
|| to_char(extract('timezone_hour' from date),'S00')
||':'
|| to_char(extract('timezone_minute' from date),'FM00')

This form is typically used in datetime fields in xml files, and
somehow I was expecting a pre-existing format for it, such as php5's
date("c") rather than the complex expression above :)

Best regards,

--
Daniel

Sam Mason wrote:

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.

Thanks, I was aware already that "timestamp with time zone" doesn't
store any timezone contrary to what its name seems to imply. (Though
when I first started with PG some years ago, I sure fell in that trap)

Best regards,

--
Daniel

2009/2/26 Daniel Verite <daniel@manitou-mail.org>:

       Pavel Stehule 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

I can't find a way to extract the offset against GMT from the docs

here:

http://www.postgresql.org/docs/8.3/static/functions-formatting.html

If not, what would be the way to convert a timestamp to such a

string

regardless of the session's datestyle settings?

try to look on function extract, there you can get timezone from any
timestamp with time zone.

Thanks, I've come up with this expression, then:

to_char(date, 'YYYY-MM-DD')
|| 'T'
|| to_char(date, 'HH24:MI:SS')
|| to_char(extract('timezone_hour' from date),'S00')
||':'
|| to_char(extract('timezone_minute' from date),'FM00')

This form is typically used in datetime fields in xml files, and somehow I
was expecting a pre-existing format for it, such as php5's date("c") rather
than the complex expression above :)

hello

you can use integrated functionality

create or replace function iso_timestamp(timestamp with time zone)
returns varchar as $$
select substring(xmlelement(name x, $1)::varchar from 4 for 32)
$$ language sql immutable;

select iso_timestamp(current_timestamp);
iso_timestamp
----------------------------------
2009-02-26T16:39:19.592113+01:00
(1 row)

regard
Pavel Stehule