# Search datetime column for all records that start at 11:00

**URL:** <https://www.ruby-forum.com/t/search-datetime-column-for-all-records-that-start-at-11-00/182135>\
**Category:** Ruby\
**Created:** [January 23, 2010, 5:05am UTC](https://www.ruby-forum.com/t/search-datetime-column-for-all-records-that-start-at-11-00/182135 "2010-01-23T05:05:27Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![morgan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/0ea827/32.png) [@morgan](https://www.ruby-forum.com/u/morgan)\
**Post date:** [January 23, 2010, 5:05am UTC](https://www.ruby-forum.com/t/search-datetime-column-for-all-records-that-start-at-11-00/182135/1 "2010-01-23T05:05:27Z")

</div>

i have to count how many records are in a database that have a start  
time of 11:00 for an entire year. the problem is, i can’t seem to find  
out how to coax rails into ignoring the date to just search for a time.

---

<div class="post-metadata">

**Author:** ![morgan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/0ea827/32.png) [@morgan](https://www.ruby-forum.com/u/morgan)\
**Post date:** [January 23, 2010, 8:04am UTC](https://www.ruby-forum.com/t/search-datetime-column-for-all-records-that-start-at-11-00/182135/2 "2010-01-23T08:04:47Z")

</div>

Morgan M. wrote:

> i have to count how many records are in a database that have a start  
> time of 11:00 for an entire year. the problem is, i can’t seem to find  
> out how to coax rails into ignoring the date to just search for a time.

If I understand your question correctly, then this is handled  
differently by different databases.

For example, mysql uses DATE\_FORMAT/TIME\_FORMAT, sqlite uses STRFTIME,  
and PostgreSQL uses the powerful extract/to\_char. See the manual for  
your specific database on how to extract and format parts of the a  
datetime column for use it in comparisons/queries.

From rails, you could specify this custom SQL as a :condition to find,  
or use execute(sql).

The last time I needed similar functionality, I didn’t find any built-in  
methods to accomplish this in rails. Would like to know if one exists  
and I somehow missed it.

hth,  
ammar

---

<div class="post-metadata">

**Author:** ![morgan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/0ea827/32.png) [@morgan](https://www.ruby-forum.com/u/morgan)\
**Post date:** [January 23, 2010, 8:55am UTC](https://www.ruby-forum.com/t/search-datetime-column-for-all-records-that-start-at-11-00/182135/3 "2010-01-23T08:55:41Z")

</div>

On 2010-01-23, Morgan M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> i have to count how many records are in a database that have a start  
> time of 11:00 for an entire year. the problem is, i can’t seem to find  
> out how to coax rails into ignoring the date to just search for a time.

The simplest solution: Do that part of the check yourself, so just have  
Rails hand you the whole query and you do the heavy lifting.

-s

---

<div class="post-metadata">

**Author:** ![morgan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/0ea827/32.png) [@morgan](https://www.ruby-forum.com/u/morgan)\
**Post date:** [January 23, 2010, 1:06pm UTC](https://www.ruby-forum.com/t/search-datetime-column-for-all-records-that-start-at-11-00/182135/4 "2010-01-23T13:06:16Z")

</div>

Ammar A. wrote:

> Morgan M. wrote:
> 
> > i have to count how many records are in a database that have a start  
> > time of 11:00 for an entire year. the problem is, i can’t seem to find  
> > out how to coax rails into ignoring the date to just search for a time.
> 
> If I understand your question correctly, then this is handled  
> differently by different databases.
> 
> For example, mysql uses DATE\_FORMAT/TIME\_FORMAT, sqlite uses STRFTIME,  
> and PostgreSQL uses the powerful extract/to\_char. See the manual for  
> your specific database on how to extract and format parts of the a  
> datetime column for use it in comparisons/queries.
> 
> From rails, you could specify this custom SQL as a :condition to find,  
> or use execute(sql).
> 
> The last time I needed similar functionality, I didn’t find any built-in  
> methods to accomplish this in rails. Would like to know if one exists  
> and I somehow missed it.
> 
> hth,  
> ammar

from what i can tell it’s not directly possible to search for a time  
match without a full date/time. i’m already using sphinx for others  
stuff and ended up using sphinx for this also. tried doing the search  
for an entire year other ways but they were all stupidly slow. sphinx  
is it

---

<div class="post-metadata">

**Author:** ![morgan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/0ea827/32.png) [@morgan](https://www.ruby-forum.com/u/morgan)\
**Post date:** [January 23, 2010, 5:47pm UTC](https://www.ruby-forum.com/t/search-datetime-column-for-all-records-that-start-at-11-00/182135/5 "2010-01-23T17:47:51Z")

</div>

Morgan M. wrote:

> i have to count how many records are in a database that have a start  
> time of 11:00 for an entire year. the problem is, i can’t seem to find  
> out how to coax rails into ignoring the date to just search for a time.

Simple: use the SQL functions hour() and minute() in your :conditions  
hash.

Note that Rails questions should really go on the Rails list, not here.

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid