Thread: Format string for ISO-8601 date and time
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? Thanks, -- Daniel
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 > > Thanks, > > -- > Daniel > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/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/
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 > Best regards, > > -- > Daniel > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >