Rails auto round millisecond query from postgresql

Question

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)

Thanks for your response. Yes, Aws1 is model of table c1.hour. My first query is same value with this. But i want to get real value as second query. — Khanh Pham
– Khanh Pham, Commented Feb 26, 2016 at 5:37
Yes, i only type Aws1.where(f: 22).order(t: :desc).first and it will return above record. — Khanh Pham
– Khanh Pham, Commented Feb 26, 2016 at 6:08
What type is column v? Can you show us the output of \d c1.hour? — Paul A Jungwirth
– Paul A Jungwirth, Commented Feb 26, 2016 at 6:12
Sorry, i updated my table hour. Please refresh your browser. Thank you. — Khanh Pham
– Khanh Pham, Commented Feb 26, 2016 at 6:14

Aleksei Matiushkin · Accepted Answer · 2016-02-26 06:47:40Z

1

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
}

answered Feb 26, 2016 at 6:47

Aleksei Matiushkin

121k12 gold badges109 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Khanh Pham Over a year ago

Thank for your response. I totally agree with your answer, but i used as Aws1.find_by_sql("...").

Aleksei Matiushkin Over a year ago

Yes, Aws1.find_by_sql("...") is even better.

Collectives™ on Stack Overflow

Rails auto round millisecond query from postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related