1

I'm using RoR and mysql database. I want to convert timestamp column to string, so i have the SQL:

@books = Book.find_by_sql("select id, name, date_format(borrow_date, '%m/%d/%Y') borrow_date from books")
@books[0].borrow_date  => 'Sun, 03 Aug 2014 17:00:00 PDT -07:00'

When i output the borrow_date like @books[0].borrow_date, i got un-formatted string 'Sun, 03 Aug 2014 17:00:00 PDT -07:00' which is not expected. I want "08/03/2014"

If i change alias name that different than the column name, i get expected data string.

@books = Book.find_by_sql("select id, name, date_format(borrow_date, '%m/%d/%Y') borrow_date_other from books")
@books[0].borrow_date_other  => "08/03/2014"

I don't want to format date string in ruby code since i need to pull the same data from oracle database and just want to convert to string before use.

Why RoR doesn't allow same column name and alias name? Do i miss something? Thank you in advance.

Morris
  • 71
  • 8

1 Answers1

2

You are actively loading Books from the database, so AREL tries to map your select statement to the model. In this case, it maps the date to the borrow_date attribute and converts it into a Date object. The original formatting you selected is then no longer relevant as it was parsed to an object which has its own rules for printing the date.

Your second select works since there probably is no attribute of that name on your Book model, so it cannot be mapped to a specific type, so plain String is used.

You could simply add a function like formatted_borrow_date to your model or use a helper to have it format the borrow_date using ruby methods to avoid the hardcoded SQL and date formatting.

KappaNossi
  • 2,656
  • 1
  • 15
  • 17
  • Thank you so much. It helps a lot. I know I should not hardcode date format, but i get error when i query date type data from ORACLE database. The error say "OCIError: ORA-01805: possible error in date/time operation". So i am forced to convert date to hardcode string. – Morris Apr 09 '14 at 08:28
  • Seems like a strange error when just selecting some rows. You should definitely try to fix it to avoid the same problems with other models. Just be aware that your date format is ambiguous and you are lucky that Ruby does the correct parsing. The Euro-style switches day and month, so 08/03/2014 could as well be March 8th. While the format %Y/%m/%d is technically also ambiguous, it is more or less regarded as the most recognized standard since Y/d/m is hardly in use. http://stackoverflow.com/questions/14755425/what-are-the-standard-unambiguous-date-formats – KappaNossi Apr 09 '14 at 08:41