CODEWITHSUNDEEP
rubypostgresqlactiverecord
user1499386
user1499386

Reputation: 1

Translate postgres VARCHAR query into ruby activerecord

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

Upvotes: 0

Views: 195

Answers (1)

mu is too short
mu is too short

Reputation: 434585

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.

Upvotes: 1

Related Questions