Thread: Date Anomaly??
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!? Thanks! ----------------------------------------------------------------------- Thomas Good e-mail: tomg@sqlclinic.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.
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. http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-set.html Ian Barwick barwick@gmx.net
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 M�nchen 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@sqlclinic.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.
Thomas Good <tomg@sqlclinic.net> writes:
> 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 ;-)
That's only setting a substyle --- one that's not relevant to the ISO
major style (at least not on output). See the SET reference page.
regards, tom lane
On Wed, 7 May 2003, Tom Lane wrote: > Thomas Good <tomg@sqlclinic.net> writes: > > 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 ;-) > > That's only setting a substyle --- one that's not relevant to the ISO > major style (at least not on output). See the SET reference page. Tom, So the closest approximation to the default "postgres[ql]" date style of MM-DD-YYYY (note delimiters) is: SQL,US which returns a MM/DD/YYYY 00:00:00 value when doing some date arithmetic? Have I got it right? Egads...in addition to calling substr() to lose the 00:00:00 (not terribly useful in this context) I'll have to call perl's binding operator (=~) to swap in hyphens as delimiters. Bottom line: there is no way to return a MM-DD-YYYY value? I never realised that the morphology of the traditional Postgres date was this complex (its declension was not apparent until I used the INTERVAL keyword) and so I've conditioned my users to using hyphens as delimiters...argh. Should have used slashes I suppose. Oh well. Thanks for the help. ----------------------------------------------------------------------- Thomas Good e-mail: tomg@sqlclinic.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.
Thomas, > > > 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 ;-) Yeah, it's my fault. Here in SF we're never sure what year it is, so we put the year first. <grin> > Bottom line: there is no way to return a MM-DD-YYYY value? Really easy, actually: SELECT to_char(datefield, 'MM-DD-YYYY'); > I never realised that the morphology of the traditional Postgres date > was this complex (its declension was not apparent until I used the > INTERVAL keyword) and so I've conditioned my users to using hyphens > as delimiters...argh. Should have used slashes I suppose. That's the price you pay for having "real" dates instead of the delimited string stored by other, less sophisticated, database systems. One of the things on my todo list (Item #18, though) is a pl/perl function that will accept any reasonable US date and return ISO standard for insert; I'll post it to the list when I'm done (maybe next week). -- Josh Berkus Aglio Database Solutions San Francisco
On Wed, May 07, 2003 at 11:04:28 -0400, Thomas Good <tomg@sqlclinic.net> wrote: > > Bottom line: there is no way to return a MM-DD-YYYY value? You can always use to_char to return the date as a string in a format you want.
On Wed, 7 May 2003, Thomas Good wrote: > On Wed, 7 May 2003, Tom Lane wrote: > > > Thomas Good <tomg@sqlclinic.net> writes: > > > 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 ;-) > > > > That's only setting a substyle --- one that's not relevant to the ISO > > major style (at least not on output). See the SET reference page. > > Tom, > > So the closest approximation to the default "postgres[ql]" date style > of MM-DD-YYYY (note delimiters) is: SQL,US which returns a > MM/DD/YYYY 00:00:00 value when doing some date arithmetic? I think you're getting a timestamp after doing that date arithmetic. Does casting it back to a date help any?
On Wed, 7 May 2003, Josh Berkus wrote: Hey Josh, how goes? > > Bottom line: there is no way to return a MM-DD-YYYY value? > > Really easy, actually: > SELECT to_char(datefield, 'MM-DD-YYYY'); Thanks for this (thanks to Bruno and Tom as well!) This does the trick for me: SELECT TO_CHAR(CURRENT_DATE - INTERVAL '1 MONTH','MM-DD-YYYY'); > One of the things on my todo list (Item #18, though) is a pl/perl function > that will accept any reasonable US date and return ISO standard for insert; > I'll post it to the list when I'm done (maybe next week). Excellent! ----------------------------------------------------------------------- Thomas Good e-mail: tomg@sqlclinic.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.
Thomas Good <tomg@sqlclinic.net> writes:
> Bottom line: there is no way to return a MM-DD-YYYY value?
Certainly:
regression=# set DateStyle TO postgresql,us;
SET
regression=# select 'today'::date; date
------------05-07-2003
(1 row)
Perhaps you are confused by the difference between timestamp and date?
regards, tom lane