random technical thoughts from the Nominet technical team

Date and Time formating issues in Ruby on Rails

1 Star2 Stars3 Stars4 Stars5 Stars (21 votes, average: 4.62 out of 5)
Loading ... Loading ...
Posted by miquel on Jun 14th, 2007

While working in a rails project which involved several date and time fields, I hit some formating issues. After some research on the net and following a few forum threads related to dates and times in RoR, I come up with a partial solution to my problems in this post:


The article explains the problem for dates and I needed a solution for both, dates and times. I also wanted to simplify the solution so I dug on to the root of the problem and came up with a different solution.

I am describing in this post how I fixed the problem for dates and times when we want other than the default format in a rails application. I separated the issues into two groups Output and Input. Output is when rails gets a date or datetime object from database and converts it to string to show it on a view. Input is when rails reads the string representing a date or time from a text field and converts it to a date or time object. However both are interrelated and at some point both involve the same objects and method calls.

Output formating problem

If we have the date 20/06/2007 on a date field in your database and want to show it textually on a view it is presented in the format “2007-06-20″, similarly if you have a datetime field you get something like “Wed Jun 20 20:30:00 +0100 2007″

This may not be the format we want when displaying dates and times, so we look at ways to change the default format. The default format can be changed by setting the preferred date display format into ./config/environment.rb:

    merge!(default => '%d/%m/%Y')
    merge!(default => '%d/%m/%Y %H:%M')

This will make Rails to show dates on the format you have chosen. However as Rails uses the default date format (which we just changed) to store dates to the DB, if your DB does not understand the format you set then null values will be stored.

The problem in this case is (at version 1.15.3 of activerecord) located in the file lib/active_record/connection_adapters/abstract/quoting.rb and can be fixed by dropping the [quoting_patch.rb] file into your application lib directory and add the following to your ./config/environment.rb file:

# Apply patch for date and date times quoting
    send(:include, QuotingPatch)

This patch corrects both, date and datetime conversion to string and quoting for the database.

Input formating problem

If we use a text field to input a date and enter “10/06/2007″ we get into the database “2007-10-06″. So the parser has interpreted the string as an American date format and set the month to be 10. If we write into the field “20/06/2007″ The parser returns null as 20 is not a good enough month and the database gets a null or fails if null are not permitted. A similar problem arises if we introduce times such as “10/06/2007 20:44″

Rails date handling when reading from a field calls ActiveRecord::ConnectionAdapters::Column.string_to_date(string) which in turn calls ParseDate.parsedate(string), but the parser does not have the current display format into account. The method tries to guess the date as best as it can but some formats are highly ambiguous. Analogously, when a time is being handled, a similar problem appears as rails calls ActiveRecord::ConnectionAdapters::Column.string_to_time(string).

To solve these issues we need to change the behavior of the methods




so they first try to match the date with the display format we have set as mentioned above. I do this by parsing the date using the default format with the DateTime.strptime method.

This solution alone, however, creates a new problem: The method string_to_date and string_to_time are called twice on the column class, one when the date is loaded from the database and another when it is loaded from a form field. Therefore the first call will carry a string in the form "yyyy-mm-dd" for a date field or "yyyy-mm-dd hh:mm:ss" in case of a datetime field which is the format the database returns. But in the second case the string will be on the default format mentioned above "dd/mm/yyyy" or "dd/mm/yyyy hh:mm" as the user is expected to input it on the field.

To avoid this problem, both patched methods fall back to the guessing procedure when the date or time fails to be matched to the default format so returning correct values in both cases.

To override the string_to_date method just drop the patch file [column_patch.rb] into your lib directory and require it from your ./config/environment.rb:

# Apply patch for date and date input
require 'column_patch'

This have been tested with "%d/%m/%Y" and "%d/%m/%Y %H:%M" formats, let me know the results of your experiences with other formats.

16 Responses

  1. Free as in Time » Blog Archive » RoR Tuesday 19/06/2007 Says:

    [...] the same vein, I didn’t want to lose track of this post on Date and Time formating issues in Ruby on Rails. Sending dates and times back and forth between MySQL and RoR has given me some surprises in the [...]

  2. Yuriy Says:

    Warning: gethostbyaddr() [function.gethostbyaddr]: Address is not a valid IPv4 or IPv6 address in /var/httpd2/wordpress/tech/wp-content/plugins/postratings/postratings.php on line 674
    when ranked your post

  3. Tom Styles Says:

    My view is that formatting of dates is such an individual thing that changing the way the default rails to-string behaviour works will only solve some of your problems. Anyway date formatting is definitely a View thing and Ruby provides such a rich date formatting tool in time.strftime that you are better using that inside your views on a case by case basis rather than try to have a one format fits all policy. see http://ruby-doc.org/core/classes/Time.html#M000297
    for more info about time.strftime

  4. Miquel Says:

    I agree with Tom in that formatting is a view matter. However the underlaying rails conventions make use of the default formatting and you need to be able to adapt it in order to use those conventions.

  5. Ahsan Says:

    Good stuff, works like a dream.

    Thanks man.

  6. Ahsan Says:

    I needed to change the date format after my project was finished, and I needed a quick fix.

    This method works perfectly for me.

    However, I had to change:
    require ‘column_patch’
    require ‘lib/column_patch’

    as I was having trouble with
    ‘script/console RAILS_ENV=production’ – it couldn’t detect column_patch….

  7. João Vitor Lacerda Guimarães Says:

    Thanks for this solution.

    It is working like a charm.

    The only thing that I choose to change is that instead of replicating the old_quote inside the new_quote method I just changed the case to be like this.

    case value
    when Time, DateTime then “‘#{quoted_date_time(value)}’”
    when Date then “‘#{quoted_date(value)}’”
    else old_quote value, column

  8. Henrique Says:

    Was this fixed in Rails 2.0 ?

  9. Paulo Fagiani Says:

    Hi Miquel,

    I’m trying to apply this to a rails project. I’ve followed all the steps and the only problem is that the date is still being converted as US date on the way back to the DB.

    I need to use dd/mm/yyyy and the conversion is being done as mm/dd/yyyy.

    When I manually update the date on the DB I can see it as dd/mm/yyyy at the UI anywhere.

    The only difference between what you explain here and my setup is at the environment.rb where it reads here:

    merge!(default => ‘%d/%m/%Y’)

    I’ve added “:” to default as it has thrown an error without it.

    am I missing anything? Thanks!

  10. Brian Mosher Says:

    This may be off topic but it apears to be in the ballpark ?

    I have the default date setup and I’m trying to save a date field to a MySql DB with

    #@purchase_time = Date.new(params[:date]['print_year'].to_i, params[:date]['print_month'].to_i, params[:date]['print_day'].to_i)

    From what the doc’s say this accepts three args ? — This saves only the month and day not the year ?

  11. Otto Domínguez Says:

    Works very well! I have had two days of learning Active Scaffold, CalendarDateSelect, and I could not find a way to make it work dd/mm/yyyy all the way from database read to database write with edit in between. With your patch you made these two days worthwhile.

  12. Gennady Says:

    Thanks for solution

  13. Nick Says:

    Does this solution work in rails 2.0? I’m getting a error when I try to run script/server after enterting the first bit of code in enviroment.rb

  14. Miquel Says:

    I think the quoting problem is already fixed in Rails 2.0. You just need to drop column_patch.rb into your lib dir and create the file ROOT_APP/config/initializers/date_formats.rb

    containing the following:

    merge!(:default => ‘%d/%m/%Y’)

    merge!(:default => ‘%d/%m/%Y %H:%M’)

    # Apply patch for date and date input
    require ‘column_patch’

  15. Ravitej Says:

    Thanks Miquel

    Your suggestion works.

    I just wonder when the output has been fixed in Rails 2.0 why hasn’t the column_patch for the input still needs the patch?

  16. Tweak CalendarDateSelect plugin to work with European datetime format « Programming is FUN! Says:

    [...] change default Date._parse method – I did not want to do that. Some say that you need to change ActiveRecord::ConnectionAdapters::Column:string_to_date method. The problem with that approact is that by the time you get into [...]

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.

Recent Posts

Highest Rated