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. However, after initdb sets it to whatever it wants, you can change it yourself in the postgresql.conf.
@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. 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 ;)
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.
Best Answer
I'm not a PostgreSQL guru, but according to PostgreSQL docs:
Now, looking at Section 8.5.2 Date/time Output
If you want an output format as YMD, you should use ISO instead of SQL.
db<>fiddle here