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

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.

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

On 2010-01-23, Morgan M. [email protected] 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

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

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
[email protected]

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs