pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres 7.3 migrate to 8.0 date problems.
4+ messages / 4 participants
[nested] [flat]
* Postgres 7.3 migrate to 8.0 date problems.
@ 2005-03-28 19:44 Thomas Seeber <thomas.seeber@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Thomas Seeber @ 2005-03-28 19:44 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres 7.3 migrate to 8.0 date problems.
@ 2005-03-28 20:48 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Thomas Seeber <thomas.seeber@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Scott Marlowe @ 2005-03-28 20:48 UTC (permalink / raw)
To: Thomas Seeber <thomas.seeber@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres 7.3 migrate to 8.0 date problems.
@ 2005-03-30 21:23 Robert Treat <xzilla@users.sourceforge.net>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 4+ messages in thread
From: Robert Treat @ 2005-03-30 21:23 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Thomas Seeber <thomas.seeber@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres 7.3 migrate to 8.0 date problems.
@ 2005-03-31 03:11 Michael Fuhr <mike@fuhr.org>
parent: Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 0 replies; 4+ messages in thread
From: Michael Fuhr @ 2005-03-31 03:11 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; Thomas Seeber <thomas.seeber@gmail.com>; pgsql-sql
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() RETURNS trigger 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 you need 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/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-03-31 03:11 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-28 19:44 Postgres 7.3 migrate to 8.0 date problems. Thomas Seeber <thomas.seeber@gmail.com>
2005-03-28 20:48 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-03-30 21:23 ` Robert Treat <xzilla@users.sourceforge.net>
2005-03-31 03:11 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox