Luke Melia

software dev

May 17, 2006

Formatting a date in a SQL query in Rails

If you need to use a date/time in a SQL fragment in Ruby on Rails you can format it using the to_formatted_s method.

    num_entries_in_last_24_hours = Entry.count(:conditions => "created_at < '#{24.hours.ago.to_formatted_s(:db)}'")

This works for mysql, not sure about other databases. Here’s the relevant API link.

4 Responses to “Formatting a date in a SQL query in Rails”

  1. Ryan Daigle chimed in:

    Isn’t it more portable to use the “?” notation:

    num_entries_in_last_24_hours = Entry.count(:conditions => [“created_at

  2. lukemelia chimed in:

    Hmm, looks like Ryan’s comment got cut off — I assume he’s suggesting something like:

    Entry.count(:conditions => ["created_at < ?", 24.hours.ago.to_formatted_s(:db)])

    And, yep, that seems like a better way to go!

    Luke

  3. Brent chimed in:

    When you use the ? notation, you don’t need to format the string.

    Entry.count(:conditions => [“created_at

  4. sergeych chimed in:

    When you use Entry.connection.select_values(“SELECT e.something FROM entries e WHERE…” you do need to convert, there is no ‘?’ args there. And sometimes you really need it to get a reasonably fast and effective code

Leave a Reply

LukeMelia.com created 1999. ··· Luke Melia created 1976. ··· Live With Passion!