2009/2/26 Daniel Verite <daniel@xxxxxxxxxxxxxxxx>: > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
