Thread: Getting 2002-12-18T17:32:40-05:00 (ISO 8601) from to_date()
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@alert.infoplease.com> Pearson Education/Information Please
Karl DeBisschop wrote: > I'd like to get an ISO 8601 date from postgresql in the following form: > > 2002-12-18T17:26:04-05:00 > > Second is the 'T' after the date part (where you might otherwise have a > space). Quote from the docs: Ordinary text is allowed in to_char templates and will be output literally. You can put a substring in double quotes to force it to be interpreted as literal text even if it contains pattern keywords. For example, in '"Hello Year: "YYYY', the YYYY will be replaced by year data, but the single Y will not be. So, try # select to_char(now(),'YYYY-MM-DD"T"HH24:MI:SS'); -- Peter Gibbs EmKel Systems
Karl DeBisschop <kdebisschop@alert.infoplease.com> writes:
> Second is the 'T' after the date part (where you might otherwise have a
> space).
This seems to be a bug in the to_date parser:
regression=# select to_char(now(),'YYYY-MM-DDT HH24:MI:SS');
to_char
----------------------
2002-12-19T 10:32:05
(1 row)
regression=# select to_char(now(),'YYYY-MM-DD THH24:MI:SS');
to_char
----------------------
2002-12-19 T10:32:08
(1 row)
regression=# select to_char(now(),'YYYY-MM-DDTHH24:MI:SS');
to_char
-----------------------
2002-12-19THH24:32:10
(1 row)
If the first two both work, I don't see why the third case shouldn't ...
regards, tom lane
Tom Lane wrote:
> regression=# select to_char(now(),'YYYY-MM-DDTHH24:MI:SS');
> to_char
> -----------------------
> 2002-12-19THH24:32:10
> (1 row)
>
> If the first two both work, I don't see why the third case shouldn't ...
Try:
select to_char(now()+'3 days'::interval,'YYYY-MM-DDTHH24:MI:SS');
to_char
-----------------------
2002-12-22NDH24:41:58
(1 row)
--
Peter Gibbs
EmKel Systems
"Peter Gibbs" <peter@emkel.co.za> writes:
> select to_char(now()+'3 days'::interval,'YYYY-MM-DDTHH24:MI:SS');
> to_char
> -----------------------
> 2002-12-22NDH24:41:58
Oh, duh --- "TH" is a format modifier. That's what's confusing it
(and today being the 19TH is what's confusing us).
So the suggestion of ... DD"T"HH ... is the way to go.
regards, tom lane
On Thu, 2002-12-19 at 10:28, Peter Gibbs wrote: > Karl DeBisschop wrote: > > > I'd like to get an ISO 8601 date from postgresql in the following form: > > > > 2002-12-18T17:26:04-05:00 > > > > Second is the 'T' after the date part (where you might otherwise have a > > space). > > Quote from the docs: > Ordinary text is allowed in to_char templates and will be output literally. > You can put a substring in double quotes to force it to be interpreted as > literal text even if it contains pattern keywords. For example, in '"Hello > Year: "YYYY', the YYYY will be replaced by year data, but the single Y will > not be. > > So, try > # select to_char(now(),'YYYY-MM-DD"T"HH24:MI:SS'); > > -- > Peter Gibbs > EmKel Systems Thanks very much. I read the docs 3 or 4 times, but must have developed a blind spot to that passage. As one might expect, to_char does exactly what is says it does, and your solution works perfectly. No bites on the offset from UTC yet, but this definitely cleans up some code. -- Karl DeBisschop <kdebisschop@alert.infoplease.com> Pearson Education/Information Please
On Thu, Dec 19, 2002 at 10:50:51AM -0500, Tom Lane wrote:
> "Peter Gibbs" <peter@emkel.co.za> writes:
> > select to_char(now()+'3 days'::interval,'YYYY-MM-DDTHH24:MI:SS');
> > to_char
> > -----------------------
> > 2002-12-22NDH24:41:58
>
> Oh, duh --- "TH" is a format modifier. That's what's confusing it
> (and today being the 19TH is what's confusing us).
# select to_char(now(),'DDth Mon YYYY');
to_char
---------------
23rd Dec 2002
> So the suggestion of ... DD"T"HH ... is the way to go.
Right, Tom read docs :-).
Please, use it in always if you want to put non-format modifier into
formatting strings.
# select to_char(now(),'"YYYY-MM-DD HH24:MI:SS =" YYYY-MM-DD HH24:MI:SS');
to_char
---------------------------------------------
YYYY-MM-DD HH24:MI:SS = 2002-12-23 10:33:40
Karel
PS. sorry of my delay in this discussion, I was out of town.
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/