Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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"

share|improve this question
up vote 21 down vote accepted
+200

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.

share|improve this answer

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"

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.