Re: Date Anomaly??

On Tue, 6 May 2003, Ian Barwick wrote:

> On Tuesday 06 May 2003 22:05, Thomas Good wrote:
> > Hi!
> >
> > When I run this from the psql monitor:
> >
> > SELECT SUBSTR(CURRENT_DATE - INTERVAL '1 MONTH',0,11);
> > it returns - 2003-04-06 which is the desired behaviour.
> >
> > Inside a CGI/DBI script it returns:
> >
> > Sun Apr 06
> >
> > Anyone have any idea what is going on here!?
>
> Somewhere the datestyle has been set to "PostgreSQL", probably
> somewhere in your CGI environment.

Hello Ian,

The PGDATESTYLE env var is apparently part of the problem.
It *is* set to Postgres. This is deliberate as I always thought
"Postgres" returned MM-DD-YYYY (an American date style).
And indeed if I set this env var in my .profile and ask psql to
SELECT CURRENT_DATE; it rtns: MM-DD-YYYY
However, once INTERVAL enters the picture this changes.

Another item: if I say 'export PGDATESTYLE=US' and ask psql for
the date I get back an ISO date (YYYY-MM-DD). What region of this
US is this, I wonder? Must be San Francisco (maybe Josh knows ;-)

It seems that if I set the PGDATESTYLE to ISO in my httpd.conf I get
back a sane date value (as opposed to this foolishness: Sun Apr 06...)
But how does one get back an American date?? Generally setting the
var to Postgres accomplishes this. But not so when INTERVAL is used.
And as mentioned above, setting the value to US returns ISO!

Perhaps I should move to Mnchen and use ISO? Perhaps California? ;-)
Is there any clear documentation on how to return a US date anywhere?
I have googled for it and read the Pg man page on SET but that doesn't
shed any light on this problem.

Cheers (and thanks for the reply!)
Thomas

-----------------------------------------------------------------------
Thomas Good e-mail: tomg(at)sqlclinic(dot)net
Programmer/Analyst phone: (+1) 718.818.5528
Residential Services fax: (+1) 718.818.5056
Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359

// Das ist die Kunst - wie man alles verhunzt.