Re: datestyle - Mailing list pgsql-general

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!