Getting 2002-12-18T17:32:40-05:00 (ISO 8601) from to_date()

From: Karl DeBisschop <kdebisschop(at)alert(dot)infoplease(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Getting 2002-12-18T17:32:40-05:00 (ISO 8601) from to_date()
Date: 2002-12-19 15:09:46
Message-ID: 1040310585.7570.3.camel@skillet.infoplease.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'd like to get an ISO 8601 date from postgresql in the following form:

2002-12-18T17:26:04-05:00

I have two problems. First, I don't see any way to specify time offset
from UTC -- the TZ is there, but the offset is not. Did I miss it in the
docs?

Second is the 'T' after the date part (where you might otherwise have a
space). I'd think the following would work:

| kdebisschop=# select to_char(now(),'YYYY-MM-DD\THH24:MI:SS');
| to_char
| -----------------------
| 2002-12-18THH24:26:08
| (1 row)

But it doesn't. Nor does:

| kdebisschop=# select to_char(now(),'YYYY-MM-DD\\THH24:MI:SS');
| to_char
| ----------------------
| 2002-12-18\T17:26:04
| (1 row)

The only way I've been able to get it is by concatenation:

| kdebisschop=# select
| to_char(now(),'YYYY-MM-DDT')||to_char(now(),'HH24:MI:SS');
| ?column?
| ---------------------
| 2002-12-18T17:32:40

But that seems rather an inelegant way to get something as simple as an
ISO standard date format.

Am I missing some approach here that would make this a little less
complex? Or is there a bug?

--
Karl DeBisschop <kdebisschop(at)alert(dot)infoplease(dot)com>
Pearson Education/Information Please

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Treat 2002-12-19 15:12:07 Re: trouble caused by change in 7.3 handling of '' in
Previous Message Robert Treat 2002-12-19 15:08:43 Re: trouble caused by change in 7.3 handling of '' in