Take the 2-minute tour ×
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. It's 100% free, no registration required.

Ho 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

return --> "en_CA.UTF-8"

show LC_CoLLATE

return --> "en_CA.UTF-8"

Thank you!

share|improve this question

1 Answer 1

up vote 8 down vote accepted

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 commment 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.

share|improve this answer
3  
If you want to do that for all database, you can change DateStyle in postgresql.conf: postgresql.org/docs/current/static/… –  a_horse_with_no_name Jun 21 '12 at 18:46
    
@a_horse_with_no_name - important one, as always. –  dezso Jun 21 '12 at 18:58
1  
@a_horse_with_no_name Will that work even though initdb was run under Canadian locale? I ask because that is the default in the postgresql.conf files I've seen. –  swasheck Jun 21 '12 at 19:06
    
@swasheck: as initdb is simply setting that property in postgresql.conf it should work just fine. –  a_horse_with_no_name Jun 21 '12 at 20:00
    
@a_horse_with_no_name: Thank you so much!!! Changing this setting worked for me(I also replaced all instances of the en.CA to en.US) –  Timka Jun 22 '12 at 1:12

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.