Date comparison of nearly the same values

Question

I have a table with two columns created_at and valid_from. They suppose to be the same in most of the cases in my database.

When I want to get all of them where both columns are equal I get zero records.

Order.where('created_at == valid_from').length

Order Load (3609.4ms)  SELECT "orders".* FROM "orders" WHERE (created_at <> valid_from)
=> 0 

So I took another aproach:

ord = Order.where("created_at != valid_from").first
  Order Load (741.0ms)  SELECT  "orders".* FROM "orders" WHERE (created_at != valid_from) ORDER BY "orders"."id" ASC LIMIT $1  [["LIMIT", 1]]
ord.created_at == ord.valid_from
  => false 
ord.created_at                  
  => Wed, 04 Jul 2018 11:34:44 CEST +02:00 
ord.valid_from
  => Wed, 04 Jul 2018 11:34:44 CEST +02:00 
ord.valid_from.to_i 
  => 1530696884 
ord.created_at.to_i
  => 1530696884 
ord.valid_from.to_i == ord.created_at.to_i
  => true 
ord.valid_from == ord.created_at     
  => false 
ord.created_at.to_f
  => 1530696884.26669 
ord.valid_from.to_f
  => 1530696884.2668211 

So there is the micro, tinny difference between two dates. From the Rails perspective, it's the same. For the database - not.

What would be the best approach to deal with dates with micro differences?

Answer 1

Take a look at this wonderful function of Postgres date_trunc(). :)

So basically comparison will look like

WHERE (date_trunc('second', created_at) != date_trunc('second', valid_from))

In your case, I think, rounding to "second" will be fine.

Hope it helps!