Thread: Postgres 7.3 migrate to 8.0 date problems.
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? -Tom Seeber Edison Schools
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.
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
On Wed, Mar 30, 2005 at 04:23:34PM -0500, Robert Treat wrote:
>
> 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.
I don't think that would work if the target column has type DATE,
presumably because NEW has the same type as a row of the table, so
NEW.datefield would be a DATE and the YYYY-DD-MM value would raise
an exception before the trigger was ever called.
CREATE TABLE foo ( id serial PRIMARY KEY, datefield date NOT NULL ); CREATE FUNCTION datefix()
RETURNStrigger AS $$ BEGIN RAISE INFO 'datefix'; NEW.datefield := current_date; -- for testing RETURN NEW;
END;$$ LANGUAGE plpgsql; CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE
datefix(); INSERT INTO foo (datefield) VALUES ('2005-03-30'); -- valid INFO: datefix INSERT 0 1 INSERT INTO foo
(datefield)VALUES ('2005-30-03'); -- not valid ERROR: date/time field value out of range: "2005-30-03" HINT: Perhaps
youneed a different "datestyle" setting.
Notice that the trigger wasn't called for the second INSERT.
Just brainstorming now, but if you want to keep datefield as a DATE,
then maybe you could create a view with datefield cast to TEXT and
create an appropriate rule so you can insert into the view and have
YYYY-DD-MM converted to YYYY-MM-DD:
DROP TABLE foo; DROP FUNCTION datefix();
CREATE TABLE foo ( id serial PRIMARY KEY, datefield date NOT NULL );
CREATE VIEW fooview AS SELECT id, datefield::text FROM foo;
CREATE RULE datefix AS ON INSERT TO fooview DO INSTEAD INSERT INTO foo (datefield) VALUES (to_date(NEW.datefield,
'YYYY-DD-MM'));
INSERT INTO fooview (datefield) VALUES ('2005-30-03'); INSERT 0 1 SELECT * FROM foo; id | datefield
----+------------ 1 | 2005-03-30 (1 row)
I admittedly haven't thought this through very far so it could have
problems, but it might be a starting point. On the other hand, I'm
inclined to agree with Scott Marlowe's advice: fix the data.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/