If you ever need to format timestamptz as ISO-8601, here's an efficient(ish) way.
CREATE OR REPLACE FUNCTION iso_timestamp(t timestamptz)
RETURNS TEXT
LANGUAGE SQL
AS $$ SELECT (
xpath(
'//text()',
xmlelement(name x, t)
)
)[1]::text
$$;You can then use iso_timestamp() in target lists, etc.
Happy ISO-8601-ing!
Thanks to Thomas Munro for his very handy suggestion, to wit:
SELECT replace(now()::text, ' ', 'T');
ERROR: return type mismatch in function declared to return text
DETAIL: Actual return type is xml[].
CONTEXT: SQL function "iso_timestamp"
2. why not simply use to_char ?
Could you be more specific about the invocation of to_char() that would actually do this?
I was about to work up a patch that basically added this format as an output because I hadn't figured out how to get to_char() to do it.