Rails3/jQuery UI Datepicker - Month and day are reversed on save

Question

(My problem is pretty much the same as described in this question, only that answer does not work in my case.)

---

I'm using the jQuery UI Datepicker in a Rails app with a Postgres db. The default implementation uses a mm/dd/yy date format to display the selected date, which is exactly what I want. However, after I save the record to the database, the month and day are reversed - it then displays as yy-dd-mm. So selecting 3/11/2012 tries to save as November 3rd instead of March 11th, and a date like 3/31/2012 is not saved at all, because it does not exist.

I've gone down 3 different avenues so far trying to fix this:

1) First attempt was to reformat the text field so the display looked how I wanted:

<%= f.text_field :foo, :value => (@model.foo.blank? ? '' : @model.foo.strftime('%m/%d/%Y')), class: "datepicker" %>

This displayed 03/31/2012 correctly initially, but still reversed when saving.

2) So next I tried changing the default way that dates are stored, thinking that would get around the problem. As described in the answer to this question, I added the following to config/locales/en.yml:

# config/locales/en.yml
en:
  date:
    formats:
      default: "%m/%d/%Y"

This did not make any difference at all. Next I found this question and tried creating config/initializers/date_formats.rb with these lines:

Date::DATE_FORMATS[:default]="%m/%d/%Y"
Time::DATE_FORMATS[:default]="%m/%d/%Y %H:%M"

Same as above, no difference.

3) After playing around with lots of combinations, the one thing I found that DID work was specifying the yyyy-mm-dd format in my call to the datepicker plugin - it's the opposite of what I wanted, but at least the dates can be successfully saved. So the datepicker call looks like this:

$( ".datepicker" ).datepicker({ dateFormat: 'yy-mm-dd' });

Selecting March 31st from the calendar populates the field with 2012-03-31, and still displays as 2012-03-31 after saving.

But how on earth can I get the datepicker to work with a mm/dd/yy format? I can't imagine that's a difficult thing to do, but what am I missing? Do I have to do something with how the dates are stored in Postgres? (They are specified as date, not datetime.)

Answer 1

Well it depends on how you are storing data in your database. If you happen to use RAW sql queries you can use to_date('31 Mar 2012', 'DD Mon YYYY') in your insert query.

Postgres' to_date reference

Now, on your client side, in the jquery date picker you can use

$.datepicker.formatDate('dd-M-yy', Yourdate);

I used DD Mon YYYY because it seems clearer. You can use mm/dd/yy format or other formats based on the needs. In those cases, use required format in postgre's to_date function

Kindly note, I havent tested above code. Leave comments if there are any issues or you face any troubles.

Edit:

You can use Date.parse('2011-06-18') in your rails view, before inserting data into database and set formatDate to 'yy-mm-dd' in the datepicker.

Answer 2

If you want to change the default date format globally for your database cluster you could also set the DateStyle GUC in your postgresql.conf and reload:

datestyle = 'iso, mdy'

(Which should be the default anyway.)

Note that this does not influence how dates are displayed in your app. Just how PostgreSQL interprets ambiguous input and some output options of Postgres.

---

If you check the datestyle setting in psql in an (obviously) different connection, that does not proof anything. You app may be setting a different datestyle in connection with local settings. You would have to check the setting inside your actual connection (not from psql).

If your datestyle setting is 'iso, mdy', then it should work as you want it to: date literals are interpreted month-before-day. Consider this demo (using PostgreSQL 9.1):

db=# set datestyle = 'ISO, MDY';
SET
db=# select '1.12.2012'::date;
    date
------------
 2012-01-12
(1 row)

db=# set datestyle = 'ISO, DMY';
SET
db=# select '1.12.2012'::date;
    date
------------
 2012-12-01
(1 row)

Emphasis mine.

To find out, which options are actually set by your app, you can set

log_statement = all

in your postgresql.conf and reload. Then start your app and save a date. Every single command will be logged. Don't forget to reset and reload afterwards (or your log files may grow huge):

log_statement = none

Then check your db log files what your app actually sent to the database.

---

You may also be interested in the to_date() command, that lets you input date literals in any format you like. But you shouldn't need that.

Answer 3

I ran into the same problems as the original poster. Since I have to compare dates/times against each other I did not want to change the DB fields to strings because that would require additional code and conversions later.

I ended up using before_save and attr_accessor in my model like this:

before_save    :format_date
attr_accessor  :unformatted_date

def format_date
  self.inquiry_date = Date.strptime(self.unformatted_date, "%m/%d/%Y").to_time(:utc)
end

The line in my view looks like this:

f.text_field  :unformatted_date, :value => (@foo.inquiry_date.blank? ? '' : @foo.inquiry_date.strftime('%m/%d/%Y)), :id => 'datepicker'