Postgres Pro
Downloads
Home   >   mailing lists

Thread: [GENERAL] 6.4.2 and 6.5 date usertype difference

[GENERAL] 6.4.2 and 6.5 date usertype difference

From
"Colin Price (EML)"
Date:
I have just replicated a db in 6.5 from 6.4.2.

The table selects the date differently from the identical table.
I obviously set a parameter in 6.4.2 which I now cannot find (from
mail list archive and doc as well).

Please aid someone who is tearing his hair out.
Thank you in advance,
Colin.

The table & contents :
     CREATE TABLE JUNK_QWERTY (
       id       int,
       mydate   date
     );
     insert into JUNK_QWERTY values (1, '1999-02-01');
     insert into JUNK_QWERTY values (2, '1999-03-24');
     insert into JUNK_QWERTY values (3, '1999-11-24');

6.4.2 output :
    timesheet=> select * from JUNK_QWERTY;
        id|    mydate
        --+----------
         1|1999-02-01
         2|1999-03-24
         3|1999-11-24
        (3 rows)

6.5 output :
    timesheet=> select * from JUNK_QWERTY;
        id|    mydate
        --+----------
         1|02-01-1999
         2|03-24-1999
         3|11-24-1999
        (3 rows)

==============================================================
==============================================================
6.4.2 version :
    timesheet=> select version();
        version

-------------------------------------------------------------
        PostgreSQL 6.4.2 on i586-pc-linux-gnu, compiled by gcc
2.7.2.

6.5 version :
    timesheet=> select version();
        version

--------------------------------------------------------------
        PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by gcc
2.7.2.3
        (1 row)

Re: [GENERAL] 6.4.2 and 6.5 date usertype difference

From
Herbert Liechti
Date:
"Colin Price (EML)" wrote:

> I have just replicated a db in 6.5 from 6.4.2.
>
> The table selects the date differently from the identical table.
> I obviously set a parameter in 6.4.2 which I now cannot find (from
> mail list archive and doc as well).
>
> Please aid someone who is tearing his hair out.
> Thank you in advance,
> Colin.

I think you should set the datestyle environment variable  $PGDATESTYLE
or executing the sql statement SET DateStyle TO 'whateverYouNeed'. For
further information see the Reference manual in chapter Data Types. You
will find in the section Date/Time Types an explanation how to use
the variable or sql statement.

Greetings Herbie


--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti                            E-Mail: Herbert.Liechti@thinx.ch
ThinX networked business services               Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Re: [GENERAL] 6.4.2 and 6.5 date usertype difference

From
Darold Gilles
Date:
"Colin Price (EML)" wrote:
>
> I have just replicated a db in 6.5 from 6.4.2.
>
> The table selects the date differently from the identical table.
> I obviously set a parameter in 6.4.2 which I now cannot find (from
> mail list archive and doc as well).
>

You've probably done a localization with your previous installation
of 6.4.2.

The date format in your 6.5 sample is the default format for date, and
for the 6.4.2 sample the format has been changed.

Guess it help, there's stuff on localization on documentation.

Gilles Darold

RE: [GENERAL] 6.4.2 and 6.5 date usertype difference

From
"Colin Price (EML)"
Date:
Cheers everyone.

Therefore :
    - At runtime level :
        test=> show DATESTYLE;
        test=> SET DATESTYLE TO 'ISO';
    - At boot up (/etc/rc.d/init.d/postgres) :
        Add :
             export PGDATESTYLE=ISO
        just before executing postmaster ....
    - At database level :
        $ postgres <options> <database>
            - option -e = European
        see $ man postgres
    - At user level :
        Add in to shell startup (Eg .bash_profile) :
            export PGDATESTYLE=ISO
Phew!


>
> On Tue, 29 Jun 1999, Colin Price (EML) wrote:
>
> > Date: Tue, 29 Jun 1999 21:53:24 +0200
> > From: "Colin Price (EML)" <Colin.Price@eml.ericsson.se>
> > To: pgsql-admin@postgreSQL.org
> > Subject: [ADMIN] 6.4.2 and 6.5 date usertype difference
> >
> >
> > I have just replicated a db in 6.5 from 6.4.2.
> >
> > The table selects the date differently from the identical table.
> > I obviously set a parameter in 6.4.2 which I now cannot find (from
> > mail list archive and doc as well).
> >
> > Please aid someone who is tearing his hair out.
> > Thank you in advance,
> > Colin.
> >
> > The table & contents :
> >     CREATE TABLE JUNK_QWERTY (
> >       id       int,
> >       mydate   date
> >     );
> >     insert into JUNK_QWERTY values (1, '1999-02-01');
> >     insert into JUNK_QWERTY values (2, '1999-03-24');
> >     insert into JUNK_QWERTY values (3, '1999-11-24');
> >
> > 6.4.2 output :
> >     timesheet=> select * from JUNK_QWERTY;
> >         id|    mydate
> >         --+----------
> >          1|1999-02-01
> >          2|1999-03-24
> >          3|1999-11-24
> >         (3 rows)
> >
> > 6.5 output :
> >     timesheet=> select * from JUNK_QWERTY;
> >         id|    mydate
> >         --+----------
> >          1|02-01-1999
> >          2|03-24-1999
> >          3|11-24-1999
> >         (3 rows)
> >
> > ==============================================================
> > ==============================================================
> > 6.4.2 version :
> >     timesheet=> select version();
> >         version
> >
> > -------------------------------------------------------------
> >         PostgreSQL 6.4.2 on i586-pc-linux-gnu, compiled by gcc
> > 2.7.2.
> >
> > 6.5 version :
> >     timesheet=> select version();
> >         version
> >
> > --------------------------------------------------------------
> >         PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by gcc
> > 2.7.2.3
> >         (1 row)
> >
> >
> >
>