Bounty Ended with 200 reputation awarded by ypercubeᵀᴹ
7 added 108 characters in body
source | link

Please be aware that the default setting is not locale independent. However, after initdb sets it to whatever it wants, you can change it yourself in the postgresql.conf.

For me it means that one has to find the one that fits their needs by a bit of experimenting - but the best way is to leave it at the default and always use an unambiguous input format. Two of these are 'YYYY-MM-DD' and 'YYYYMMDD'. I prefer the former - even using this IRL ;)

Note: the datestyle setting (and other runtime settings) from postgresql.conf can be overridden by an ALTER DATABASE bla SET datestyle ..., setting it permanently for a single database, or by SET datestyle TO ... setting it for the current session. This latter might be useful when importing some third party data with a different date format.

Please be aware that the default setting is not locale independent.

For me it means that one has to find the one that fits their needs by a bit of experimenting - but the best way is to leave it at the default and always use an unambiguous input format. Two of these are 'YYYY-MM-DD' and 'YYYYMMDD'. I prefer the former - even using this IRL ;)

Please be aware that the default setting is not locale independent. However, after initdb sets it to whatever it wants, you can change it yourself in the postgresql.conf.

For me it means that one has to find the one that fits their needs by a bit of experimenting - but the best way is to leave it at the default and always use an unambiguous input format. Two of these are 'YYYY-MM-DD' and 'YYYYMMDD'. I prefer the former - even using this IRL ;)

Note: the datestyle setting (and other runtime settings) from postgresql.conf can be overridden by an ALTER DATABASE bla SET datestyle ..., setting it permanently for a single database, or by SET datestyle TO ... setting it for the current session. This latter might be useful when importing some third party data with a different date format.

6 added 192 characters in body
source | link

Now this is not really expected - and it's the same on an other server with en_US.UTF8, too. Trying "ISO, DMY", too suggests me that the "ISO" part is more or less overriding the other part when producing an output. For input values, it has the expected effect, as summarized in the table below:

SET datestyle TO "ISO, YMD";

SELECT current_date;                     input values
    date       '2016/01/21'   '01/21/2016'   '21/01/2016'         output
--------──────────────────────────────────────────────────────────────────────
ISO, YMD        OK             --             --
  2012           2016-0601-21
(1 row)

SETISO, datestyleDMY TO "postgres, MDY";

SELECT current_date;
    date
----             OK             --            2016-01-21
ISO, MDY        --             --
  06            OK            2016-2101-2012
(1 row)21

-- means above that the given input style results in an error for the given datestyle setting.

Upon careful reading of the documentation, one can see that datestyle is a pair of partially conflicting settings:

For historical reasons, this variable contains two independent components: the output format specification (ISO, Postgres, SQL, or German) and the input/output specification for year/month/day ordering (DMY, MDY, or YMD).

For me it means that one has to find the one that fits their needs by a bit of experimenting - but the best way is to leave it at the default and always use an unambiguous input format. Two of these are 'YYYY-MM-DD' and 'YYYYMMDD'. I prefer the former - even using this IRL ;)

Now this is not really expected - and it's the same on an other server with en_US.UTF8, too.

SET datestyle TO "ISO, YMD";

SELECT current_date;
    date
------------
  2012-06-21
(1 row)

SET datestyle TO "postgres, MDY";

SELECT current_date;
    date
------------
  06-21-2012
(1 row)

Now this is not really expected - and it's the same on an other server with en_US.UTF8, too. Trying "ISO, DMY", too suggests me that the "ISO" part is more or less overriding the other part when producing an output. For input values, it has the expected effect, as summarized in the table below:

                          input values
           '2016/01/21'   '01/21/2016'   '21/01/2016'         output
──────────────────────────────────────────────────────────────────────
ISO, YMD        OK             --             --            2016-01-21
ISO, DMY        --             OK             --            2016-01-21
ISO, MDY        --             --             OK            2016-01-21

-- means above that the given input style results in an error for the given datestyle setting.

Upon careful reading of the documentation, one can see that datestyle is a pair of partially conflicting settings:

For historical reasons, this variable contains two independent components: the output format specification (ISO, Postgres, SQL, or German) and the input/output specification for year/month/day ordering (DMY, MDY, or YMD).

For me it means that one has to find the one that fits their needs by a bit of experimenting - but the best way is to leave it at the default and always use an unambiguous input format. Two of these are 'YYYY-MM-DD' and 'YYYYMMDD'. I prefer the former - even using this IRL ;)

5 added 192 characters in body
source | link

I used to have a very similar problem years ago, and then discovered that I can do

ALTER DATABASE database_name SET datestyle TO "ISO, MDY";

Try that. (This works on a per database basis, for a solution for all of your databases see @a_horse_with_no_name's comment below, set this parameter in you postgresql.conf - thanks to @a_horse_with_no_name.)

EDIT: Unfortunately, I cannot test other locales, butPlease be aware that the default setting is not locale independent.

@swasheck's comment made me note that with my settings:

test=# SHOW lc_ctype;
      lc_ctype
--------------------
 Hungarian, Hungary
(1 row)


test=# SHOW lc_time;
      lc_time
--------------------
 Hungarian, Hungary
(1 row)

setting datestyle has the following effect:

SET datestyle TO "ISO, MDY";

SELECT current_date;
    date
------------
 2012-06-21
(1 row)
 

Now this is not really expected - and it's the same on an other server with en_US.UTF8, too.

SET datestyle TO "ISO, YMD";

SELECT current_date;
    date
------------
 2012-06-21
(1 row)

SET datestyle TO "postgres, MDY";

SELECT current_date;
    date
------------
 06-21-2012
(1 row)

Apparently, the locale has an effect on these settings.

I used to have a very similar problem years ago, and then discovered that I can do

ALTER DATABASE database_name SET datestyle TO "ISO, MDY";

Try that. (This works on a per database basis, for a solution for all of your databases see @a_horse_with_no_name's comment below.)

EDIT: Unfortunately, I cannot test other locales, but @swasheck's comment made me note that with my settings:

test=# SHOW lc_ctype;
      lc_ctype
--------------------
 Hungarian, Hungary
(1 row)


test=# SHOW lc_time;
      lc_time
--------------------
 Hungarian, Hungary
(1 row)

setting datestyle has the following effect:

SET datestyle TO "ISO, MDY";

SELECT current_date;
    date
------------
 2012-06-21
(1 row)
 
SET datestyle TO "ISO, YMD";

SELECT current_date;
    date
------------
 2012-06-21
(1 row)

SET datestyle TO "postgres, MDY";

SELECT current_date;
    date
------------
 06-21-2012
(1 row)

Apparently, the locale has an effect on these settings.

I used to have a very similar problem years ago, and then discovered that I can do

ALTER DATABASE database_name SET datestyle TO "ISO, MDY";

Try that. (This works on a per database basis, for a solution for all of your databases, set this parameter in you postgresql.conf - thanks to @a_horse_with_no_name.)

Please be aware that the default setting is not locale independent.

@swasheck's comment made me note that with my settings:

test=# SHOW lc_ctype;
      lc_ctype
--------------------
 Hungarian, Hungary
(1 row)


test=# SHOW lc_time;
      lc_time
--------------------
 Hungarian, Hungary
(1 row)

setting datestyle has the following effect:

SET datestyle TO "ISO, MDY";

SELECT current_date;
    date
------------
 2012-06-21
(1 row)

Now this is not really expected - and it's the same on an other server with en_US.UTF8, too.

SET datestyle TO "ISO, YMD";

SELECT current_date;
    date
------------
 2012-06-21
(1 row)

SET datestyle TO "postgres, MDY";

SELECT current_date;
    date
------------
 06-21-2012
(1 row)
4 deleted 1 character in body
source | link
3 poiting to a useful comment
source | link
2 added short test on locale
source | link
1
source | link