Hi,
We were prototyping our system working with the new Postgres 7
so we set up a new machine with all the important programs and
copied all the data to it.
We used pg_dump in various ways, all with the date style "iso"
but always some of the dates appeared to be translated wrong.
Eventually we worked out that even though the datestyle was
set to "iso" on both machines, the old postgres read it as
"ISO with european conventions" whereas the new postgres read
it as "ISO with US conventions".
I'd never even heard of ISO with US conventions before (which
appears to be yyyy-dd-mm) but surely ISO is ISO format, not
with different conventions. What's worse, while the database
was reading the data in, when the date was something like
2000-07-29 it decided it wasn't US style at all and interpreted
it as european style, so *most* of the dates worked. Talk about
data corruption.
So I forced the datestyle to "european", which fixed it, but
it seems to mean "ISO with european conventions" and now setting
the datestyle back to "iso" leaves it as european. It seems that
setting the datestyle to "iso" is a no op.
This may be a documented feature, but it's still confusing.
This is the postgresql debian package 7.0.2-3.
PS. I thought we'd left behind all the US/non-US datestyle
distinction when we all started using ISO format (yyyy-mm-dd).
That was somewhat naive of me, huh?
--
Martijn van Oosterhout
