Re: Format string for ISO-8601 date and time

2009/2/26 Daniel Verite <daniel(at)manitou-mail(dot)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

> Best regards,
>
> --
> Daniel
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>