CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlruby-on-rails-4
Khanh Pham
Khanh Pham

Reputation: 2973

Rails auto round millisecond query from postgresql

I had a problem when i query exponent value from Postgresql database.

When i query as follow:

select t,v from c1.hour where f = 22 and date_trunc('day',t - '1 hour'::INTERVAL) = '2016-02-26 00:00:00' order by t ;


      t          |      v      
---------------------+-------------
 2016-02-26 02:00:00 | 1.45642e+09

It return exponent value.

And i expand exponent values as follow:

select t,to_char(v, '99999999999999999D99') from c1.hour where f = 22 and date_trunc('day',t - '1 hour'::INTERVAL) = '2016-02-26 00:00:00' order by t ;
      t          |      to_char       
---------------------+--------------------
 2016-02-26 02:00:00 | 1456418944

But when i used ActiveRecord to get data, the result seems incorrect.

#<Aws1:0x00000007940ca8 t: Fri, 26 Feb 2016 02:00:00 JST +09:00, f: 22, v: 1456420000.0>

My expected result is: 1456418944 instead of 1456420000.0.

And i didn't understand why Rails returned this result. Can I get some help on this please?

Some information: I used Rails 4.2.5, Ruby 2.2.3 and gem 'pg', '~> 0.15'

Update: This is hour table

aws1=# \d hour
             Table "c1.hour"
Column |            Type             | Modifiers 
--------+-----------------------------+-----------
t      | timestamp without time zone | not null
f      | smallint                    | not null
h      | smallint                    | not null
v      | real                        | 
q      | smallint                    | 
Indexes:
"hour_key" PRIMARY KEY, btree (t, f, h)

Upvotes: 1

Views: 125

Answers (1)

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 121000

I believe it is a PostgreSQL issue, not Rails. Rails just shows what was returned. It won’t adjust the query result by any means. As I can see, when PG is queried for this field explicitly, it returns 1.45642e+09, which is apparently exactly equals to what you see in Rails (1456420000.0.)

So, the question is to be restated as “how would one force PG to return a precise value?” Well, you have this question answered in OP: explicit type convertion to_char(v, '99999999999999999D99') is needed.

The summing up: there are two solutions I can think of:

  • introduce the new calculated field in PG, filled up by trigger as to_char(v, '99999999999999999D99'), or
  • use an explicit query in Rails.

The latter would look like:

Aws1.connection.execute %Q{
  SELECT t, to_char(v, '99999999999999999D99')
  FROM c1.hour
  WHERE f = 22 
    AND date_trunc('day',t - '1 hour'::INTERVAL) = '2016-02-26 00:00:00'
  ORDER BY t
}

Upvotes: 1

Related Questions