Rails store numbers with 2 decimal places after comma in database

Question

I am doing PDFs for invoices in my system and I would like to be able to store numbers with two decimal places in the database. I am using MoneyRails gem for dealing with currencies, I have setup precision: 10 and scale: 2 on the database level (I use postgres as my DB) but I am getting only 1 decimal place after comma. Why?

class AddPrecisionToInvoices < ActiveRecord::Migration[5.2]
  def self.up
    change_column :invoices, :total_net_amount_cents, :decimal, precision: 10, scale: 2, default: 0.00
    change_column :invoices, :total_gross_amount_cents, :decimal, precision: 10, scale: 2, default: 0.00
  end

  def self.down
    change_column :invoices, :total_net_amount_cents, :bigint
    change_column :invoices, :total_gross_amount_cents, :bigint
  end
end

invoice.rb

monetize :total_net_amount_cents
monetize :total_gross_amount_cents

In rails console,

invoice.total_gross_amount_cents = Money.new(20_000_00)
invoice.total_gross_amount.to_f #=> 2000.0

Is it possible to store numbers with two decimal places in DB, like 20,000.00? I don't want to display the PDF in a view so I want to be able to drop the number into my DB as I got it from params from my front-end application without further formatting it in a view.

You are not storing numbers with two decimal places. The `.` is a decimal, the `,` is a thousand separator. If you're looking to store the value as submitted completely unaltered then you need to store it as a string. The value of that data is then more limited, e.i. can't be summed etc. — R. Hatherall, Jan 04 '19 at 13:05
I checked the value in `psql` console and it's saved as 1200.00, with two decimal places so it's correct. I just needed a way to format the value properly after retrieving it from DB as `money-rails` converts the value to a Money object. I can call `value.format` to a get a value suitable for dispalying it but not suitable for calculations. I need to figure this out still. — jedi, Jan 05 '19 at 19:17
My apologies @jedi, I read your question two fast. I assumed you wanted all the formatting stored (e.g. the comma and the period). — R. Hatherall, Jan 08 '19 at 15:16
No probs. I just want to keep the decimals with two digits. Stil lhaven't found a solution yet. — jedi, Jan 08 '19 at 15:21

ray · Answer 1 · 2019-01-04T12:59:26.927

0

You can try following, (using in model)

ActiveSupport::NumberHelper::number_to_delimited('%.2f' % '3423432.43234', delimiter: ",", separator: ".")

# => "3,423,432.43"

Here, in above input 3423432.43234 is provided as string, you can provide it as number also.

You can directly use number_with_delimiter in view

edited Jan 04 '19 at 12:59

answered Jan 04 '19 at 12:39

ray

5,454
1
18
40

Sorry, I have not been very precise in my question and I have updated it now. I don't want to use it in a view becuase I just create a PDF and then upload it to Amazon so no further formatting in a view, etc. Does it make sense? – jedi Jan 04 '19 at 13:02
formatted number with commas can be stored in table only as a string but if you retrieve them back for calculation you have to convert them by removing commas for operation. – ray Jan 04 '19 at 13:05
I suggest you to store these values in table as float. But whenever you want to display in web view or PDF template, process it as I said. – ray Jan 04 '19 at 13:06
`"200,00".to_f #=> 200.0`, `"200.00".to_f #=> 200.0` so storing the value as a string for further processig won't help, I guess. – jedi Jan 04 '19 at 13:27
@jedi why do you think `to_f` is intelligent one, try `'2334,343,44'.gsub(',','')` – ray Jan 04 '19 at 13:27
OK so `'2334,343,44'.gsub(',','').to_f #=> 233434344.0` – jedi Jan 04 '19 at 13:30
No need of `to_f` here as if you store `int` data in `float` datatype column of table, it will get automatically converted in float – ray Jan 04 '19 at 13:32
Yes, but it will not give me `200.00` but `200.0` – jedi Jan 04 '19 at 13:35
It make no sense. Let it be in that format. When you want to display, you can have number of zeros to display as per my code provided. by logic, 200.0 == 200.00000, so do not worry – ray Jan 04 '19 at 13:38

score 0 · Answer 2 · answered Jan 04 '19 at 13:41

The money-rails gem requires monetize columns to be numeric in the database. However, it comes with some helper methods that you could use to re-format as you wish in your model:

# inside Invoice model
require "money-rails/helpers/action_view_extension"
class Invoice < ApplicationRecord
  include MoneyRails::ActionViewExtension
  # ...
  def total_gross_amount_formatted
    humanized_money total_gross_amount
  end
end

Then in your PDF you can just reference the new formatted attribute:

@invoice_instance.total_gross_amount_formatted

Rails store numbers with 2 decimal places after comma in database

2 Answers2