Postgres Pro
Downloads
Home   >   mailing lists

Thread: datestyle

datestyle

From
Martín Marqués
Date:
Hi, I'm trying to set the datesyle in postgres to european (day before
month). I'm starting postgres with pg_ctl with these options:

pg_ctl -o "-i -o -e" -D /usr/local/pgsql/data/ start

But when I select a date field I get this:

pruebas=> select * from pr_fecha;
   fecha    |  horas
------------+----------
 2001-12-25 |
 2001-12-25 |
            | 16:00:00
 2001-03-13 |
(4 rows)

pruebas=>

Shouldn't the date field be 25/12/2001?

Saludos... :-)

--
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

Re: datestyle

From
will trillich
Date:
On Mon, Jun 04, 2001 at 10:00:16AM +0300, Mart?n Marqu?s wrote:
> Hi, I'm trying to set the datesyle in postgres to european (day before
> month). I'm starting postgres with pg_ctl with these options:
>
> pg_ctl -o "-i -o -e" -D /usr/local/pgsql/data/ start
>
> But when I select a date field I get this:
>
> pruebas=> select * from pr_fecha;
>    fecha    |  horas
> ------------+----------
>  2001-12-25 |
>  2001-12-25 |
>             | 16:00:00
>  2001-03-13 |
> (4 rows)
>
> pruebas=>
>
> Shouldn't the date field be 25/12/2001?

according to /usr/share/doc/postgresql-doc/html/user/sql-set.htm
(on debian, "apt-get install postgresql-doc" does the trick):

    SET -- Set run-time parameters for session

    SET variable { TO | = } { value | 'value' | DEFAULT }

[snip]

    DATESTYLE

    Set the date/time representation style. Affects the output
    format, and in some cases it can affect the interpretation of
    input.

    ISO
    use ISO 8601-style dates and times

    SQL
    use Oracle/Ingres-style dates and times

    Postgres
    use traditional Postgres format

    European
    use dd/mm/yyyy for numeric date representations.

    NonEuropean
    use mm/dd/yyyy for numeric date representations.

    German
    use dd.mm.yyyy for numeric date representations.

    US
    same as NonEuropean

    DEFAULT
    restores the default values (ISO)

so

    database> set datestyle to German;

won't do what you want, either. :)

don't forget that internally, a date is a date. you can compare
dates, add week intervals and so forth, regardless of which
format they are displayed in. when you choose to see one
converted to a human-readable string of letters and digits,
that's when the datestyle comes in to play (and probably also
when converting a string to a date, as well).

just like adding 1 day to the midnight before changing over to
daylight savings time -- internally, times are kept in UTC (i
think). if you ask to display them in eastern standard time,
you'll get a readable string representing what clocks in that
time zone should have read at that moment. internally, time is
stored independent of time zone or display style. (which makes
sense, if you can unfold your years of time zone propaganda. :)

--
#95: We are waking up and linking to each other. We are watching. But
we are not waiting.  -- www.cluetrain.com

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Re: datestyle

From
Martín Marqués
Date:
On Lun 04 Jun 2001 17:04, you wrote:
> On Mon, Jun 04, 2001 at 10:00:16AM +0300, Mart?n Marqu?s wrote:
> > Hi, I'm trying to set the datesyle in postgres to european (day before
> > month). I'm starting postgres with pg_ctl with these options:
> >
> > pg_ctl -o "-i -o -e" -D /usr/local/pgsql/data/ start
> >
> > But when I select a date field I get this:
> >
[snip]
>
> according to /usr/share/doc/postgresql-doc/html/user/sql-set.htm
> (on debian, "apt-get install postgresql-doc" does the trick):
>
>     SET -- Set run-time parameters for session
>
>     SET variable { TO | = } { value | 'value' | DEFAULT }

From the postgres manual!

     -e   Sets the default  date  style  to  ``European'',  which
          means  that the ``day before month'' (rather than month
          before day) rule is used to  interpret  ambiguous  date
          input,  and that the day is printed before the month in
          certain date output formats. See the PostgreSQL  User's
          Guide for more information.

Now, this doesn't work with output dates?

Isn't there a way to set this so that all the connection go in with the
DATESTYLE=EUROPEAN?

Saludos... :-)

--
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

Re: datestyle

From
Peter Eisentraut
Date:
Martín Marqués writes:

> Hi, I'm trying to set the datesyle in postgres to european (day before
> month). I'm starting postgres with pg_ctl with these options:
>
> pg_ctl -o "-i -o -e" -D /usr/local/pgsql/data/ start
>
> But when I select a date field I get this:
>
> pruebas=> select * from pr_fecha;
>    fecha    |  horas
> ------------+----------
>  2001-12-25 |
>  2001-12-25 |
>             | 16:00:00
>  2001-03-13 |
> (4 rows)
>
> pruebas=>
>
> Shouldn't the date field be 25/12/2001?

No, the European option only applies if you select the "SQL" or "Postgres"
date style.  There is no such thing as "European ISO".

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Re: datestyle

From
Martín Marqués
Date:
On Lun 04 Jun 2001 12:54, you wrote:
> On Lun 04 Jun 2001 17:04, you wrote:
> > On Mon, Jun 04, 2001 at 10:00:16AM +0300, Mart?n Marqu?s wrote:
> > > Hi, I'm trying to set the datesyle in postgres to european (day before
> > > month). I'm starting postgres with pg_ctl with these options:
> > >
> > > pg_ctl -o "-i -o -e" -D /usr/local/pgsql/data/ start
> > >
> > > But when I select a date field I get this:
>
> [snip]
>
> > according to /usr/share/doc/postgresql-doc/html/user/sql-set.htm
> > (on debian, "apt-get install postgresql-doc" does the trick):
> >
> >     SET -- Set run-time parameters for session
> >
> >     SET variable { TO | = } { value | 'value' | DEFAULT }
>
> From the postgres manual!
>
>      -e   Sets the default  date  style  to  ``European'',  which
>           means  that the ``day before month'' (rather than month
>           before day) rule is used to  interpret  ambiguous  date
>           input,  and that the day is printed before the month in
>           certain date output formats. See the PostgreSQL  User's
>           Guide for more information.
>
> Now, this doesn't work with output dates?
>
> Isn't there a way to set this so that all the connection go in with the
> DATESTYLE=EUROPEAN?

More info:

pruebas=> SET DateStyle TO 'European';
SET VARIABLE
pruebas=> select * from pr_fecha;
   fecha    |  horas
------------+----------
 2001-12-25 |
 2001-12-25 |
            | 16:00:00
 2001-03-13 |
(4 rows)

pruebas=>

Any ideas?


--
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------