Thread: DateStyle causes drama during upgrade
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
Martijn van Oosterhout wrote:
>
> 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".
> 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?
I've been bitten by this too. It seems that there are two
characteristics for the dates: format (for output) and 'conventions' for
input, and that 6.5 -> 7.0 changed from defaulting to European
conventions to US conventions.
I suspect this is Debian specific.
Perhaps there should be a way of setting the conventions side of things
in the /etc/postgresql/postmaster.init like there is a way of setting
the format?
Regards,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew@cat-it.co.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
Andrew McMillan wrote:
>
> Martijn van Oosterhout wrote:
> >
> > 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".
>
> > 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?
>
> I've been bitten by this too. It seems that there are two
> characteristics for the dates: format (for output) and 'conventions' for
> input, and that 6.5 -> 7.0 changed from defaulting to European
> conventions to US conventions.
>
> I suspect this is Debian specific.
>
> Perhaps there should be a way of setting the conventions side of things
> in the /etc/postgresql/postmaster.init like there is a way of setting
> the format?
Ah! I found out now!
If you set the local in your /etc/postgresql/postmaster.init to an
appropriate one, it nearly gets it right.
If I set:
LANG=en_GB
I get european conventions, but if I leave it unset (the default) I get
US conventions.
Of course, if I set it for 'en_NZ' I get US conventions. Perhaps en_NZ
is not valid?
Cheers,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew@cat-it.co.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
Andrew McMillan wrote: > > Andrew McMillan wrote: > > > > Martijn van Oosterhout wrote: > > > > > > 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". > > > > > 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? > > > > I've been bitten by this too. It seems that there are two > > characteristics for the dates: format (for output) and 'conventions' for > > input, and that 6.5 -> 7.0 changed from defaulting to European > > conventions to US conventions. > > > > I suspect this is Debian specific. > > > > Perhaps there should be a way of setting the conventions side of things > > in the /etc/postgresql/postmaster.init like there is a way of setting > > the format? > > Ah! I found out now! > > If you set the local in your /etc/postgresql/postmaster.init to an > appropriate one, it nearly gets it right. > > If I set: > LANG=en_GB > > I get european conventions, but if I leave it unset (the default) I get > US conventions. Well, that's helpful, if you know. Silly idea. But I guess the real problem is that it doesn't require the input to be of that format, silently corrupting data... Even just a warning would have made it clear where the problem lay. > Of course, if I set it for 'en_NZ' I get US conventions. Perhaps en_NZ > is not valid? Nope, it's not: kleptog//usr/share/locale>ls -d en* en/ en_AU/ en_BW/ en_CA/ en_DK/ en_GB/ en_IE/ en_US/ en_ZW/ I guess en_AU is for you :) -- Martijn van Oosterhout <kleptog@cupid.suninternet.com> http://cupid.suninternet.com/~kleptog/