How to set Postgresql database to see date as "MDY" permanently

Question

How can I set my database to see the 'date' as "MDY" without running:

SET datestyle = "ISO, MDY";

every-time I'm trying to access it? I'm using Postgresql version 9.1, Ubuntu 12.04. My system locale, at the time of the database installation was set to en_CA.utf8 and I recently changed it to en_US.utf8.

show lc_CTYPE

returns: --> "en_CA.UTF-8"

but

show LC_CoLLATE

returns: --> "en_CA.UTF-8"

Answer 1

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 your 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 another 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
     

-- above means 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.

Answer 2

Thanks @a_horse_with_no_name| @dezso | @ypercubeᵀᴹ

I want to write answer thats simple to implement : Steps

Way 1 : setting datestyle Per Database Only

1. show datestyle; show you current date style "ISO, DMY" or "ISO, MDY"

2. Now depending on what you want in postgres DMY or MDY set in below query

   ALTER DATABASE "my_database_name" SET datestyle TO "ISO, DMY";

   or

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

3. Most important step : Always after setting datestyle restart postgres

   sudo service postgresql restart

   Or

   sudo /etc/init.d/postgresql restart

4. Lets test configuration

   if you have set DMY below query should work for you

   Query:select '20/12/2016'::date Output: "2016-12-20"

   Or

   if you have set MDY below query should work for you

   Query:select '12/19/2016'::date Output: "2016-12-19"

Way 2 : Permanently : whatever you set in config file will be set in all databases you will create in future

1. In /etc/postgresql/9.3/main/postgresql.conf set

   datestyle = 'iso, dmy' OR datestyle = 'iso, mdy'

2. Most important step : Always after setting datestyle restart postgres

   sudo service postgresql restart

   Or

   sudo /etc/init.d/postgresql restart

3. Lets test configuration

   if you have set DMY below query should work for you

   Query:select '20/12/2016'::date Output: "2016-12-20"

   Or

   if you have set MDY below query should work for you

   Query:select '12/19/2016'::date Output: "2016-12-19"

Answer 3

Expanding @deszo's answer (I do not have enough reputation to comment there), ISO parameter overrides the day-month-year specification in output. If you want to have input and output in the same format, you need to use SET DATESTYLE TO 'SQL, MDY', or SET DATESTYLE TO 'MDY, SQL' (parameter order is inferred by Postgres).