Subject: Re: Postgres 7.3 migrate to 8.0 date problems.
From: Robert Treat <xzilla@users.sourceforge.net>
To: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: Thomas Seeber <thomas.seeber@gmail.com>, pgsql-sql@postgresql.org
In-Reply-To: <1112042886.22988.12.camel@state.g2switchworks.com>
References: <578aa3f70503281144200c2404@mail.gmail.com>
	<1112042886.22988.12.camel@state.g2switchworks.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 30 Mar 2005 16:23:34 -0500
Message-Id: <1112217814.28784.419.camel@camel>
Mime-Version: 1.0

On Mon, 2005-03-28 at 15:48, Scott Marlowe wrote:
> On Mon, 2005-03-28 at 13:44, Thomas Seeber wrote:
> > Hi,
> > 
> > We were upgrading from postgres 7.3 -> 8.0 and having a little
> > problems importing dates from some of our data sources.  Say we have a
> > date like '2004-17-05'.  In postgres 7.3, postgres would intrept this
> > as Year Day Month automatically.  In the documentation, from postgres
> > 7.4 on this has to be specified in the Datestyle option and YDM is not
> > an option.  Other data we have is coming in on the YMD formate which
> > would be more expected.  I realize that this change is better for data
> > integrity, however we have alot of legacy systems where being able to
> > mimic the 7.3 behaviour would be desireable.  Any ideas?
> 
> Fix the data?  I had to write a lot of scripts to keep dates like that
> OUT of my last PostgreSQL installation, which was running 7.2  Which is
> why, as the guy who whinged and moaned until this behavioural change was
> made, I feel for you, but honestly, the lackadaisical manner of handing
> that particular format (YYYY-DD-MM) never really seemed right even to
> the people who fought me on the idea of changing the default behaviour
> of DD/MM/YYYY versus MM/DD/YYYY.  
> 
> While the US uses MM/DD/YYYY and Europe uses DD/MM/YYYY, and there may
> be some arguments for handling a sloppy version of one of those,
> computer folk (and the government) who want easily ordered dates use
> YYYY-MM-DD, I've never seen a good argument made for the usage of
> YYYY-DD-MM before.
> 
> Are you sure that the other dates in your data set are what you think
> they are?  Because if the two numbers are both <=12, then you'll get one
> "date" and if the wrong one is >12 you'll get another.  That can't be
> good.  
> 

Would it be possible to use a BEFORE trigger to reformat the YYYY-DD-MM
date to YYYY-MM-DD ?  The error I see on 7.4 is ERROR:  date/time field
value out of range: "2005-14-01" so ISTM you could do some data
manipulation if you wanted.


Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL