Translate postgres VARCHAR query into ruby activerecord

Question

I need to translate the following postgreSQL query into an activerecord ruby query.

select * 
from my_table 
where (my_time between '2010-12-27 00:00:00' and '2011-01-28 00:00:00') 
  and (my_time::TIME)::VARCHAR like '12:00:00.%';

This pulls out the 12:00:00.% piece of data for each day. I can do time range part, but don't know how to translate the second half of the query.

Thanks

Your cast might not work properly with different language settings. You should use to_char() with a format mask rather than the implicit cast. — a_horse_with_no_name, Jul 03 '12 at 16:44
Is it possible to translate this into a ruby activerecord statement? — user1499386, Jul 03 '12 at 17:00
Maybe this will help? http://stackoverflow.com/questions/4430578/how-to-do-a-like-query-in-arel-and-rails-3 — Casper, Jul 03 '12 at 17:32

score 1 · Answer 1 · answered Jul 03 '12 at 18:32

You could use to_char to convert your timestamp to the appropriate string form and strip off the fractional seconds at the same time:

where to_char(my_time, 'HH24:MM:SS') = '12:00:00'
...

The ActiveRecord version of that is pretty simple:

MyTable.where("to_char(my_time, 'HH24:MM:SS') = '12:00:00'")

Then chain in your existing BETWEEN check.

You could also use extract to check each time component separately:

where extract(hour   from my_time) = 12
  and extract(minute from my_time) =  0
  and extract(second from my_time) =  0
  ...

The ActiveRecord version of that would be:

MyTable.where('extract(hour   from my_time) = ?', 12)
       .where('extract(minute from my_time) = ?',  0)
       .where('extract(second from my_time) = ?',  0)

You'd have to ask EXPLAIN to see which version works best.

Translate postgres VARCHAR query into ruby activerecord

1 Answers1