0

My question is sort of like the one found here:

How to compare sqlite TIMESTAMP values

Except that the answer doesn't work for PostgreSQL. Is there any way to compare two created_at values made by Rails with a PG function?

Here's what I want to do (I know this doesn't work, DATETIME doesn't work for PG):

EventDish.find_by_sql(["select * from ............. and DATETIME(events.created_at) < ?", other_event.created_at])

With the ellipses added to highlight the important part. In other words, I want to compare two Rails created_at fields (these are using whatever the default is), but I want to compare them with PG. Any suggestions? (I'm not too familiar with SQL in general, so any pure Rails solutions would also be appreciated)

Thanks in advance

Community
  • 1
  • 1
varatis
  • 14,494
  • 23
  • 71
  • 114
  • are you getting a postgresql error? you can normally compare postgresql timestamp values with strings directly in sql, postgresql will implicitly convert the string to a timestamp to make the conversion. – araqnid Dec 07 '11 at 03:05

2 Answers2

1

You should be able to just do:

EventDish.where('created_at < ?', other_event.created_at)

This translates to:

"SELECT \"event_dishes\".* FROM \"event_dishes\" WHERE (created_at < '2011-06-10 16:39:19.557436')"

I would personally stay way from using find_by_sql unless you really need to do some complex queries. If you can, keep it simple and let ActiveRecord do the work for you.

If you can't avoid using find_by_sql then just update your current query to be:

EventDish.find_by_sql(["select * from ............. WHERE (created_at < '#{other_event.created_at}')")
iwasrobbed
  • 46,496
  • 21
  • 150
  • 195
  • The problem is that it is a quite complex query... I can't post it entirely here. I've tried doing event.created_at < ?, other_event.created_at but PG can't seem to get it. – varatis Dec 06 '11 at 22:21
0

Simply skip the DATETIME() function:

EventDish.find_by_sql(['select * from event_dish where created_at > ?', other_event.created_at])
maniek
  • 7,087
  • 2
  • 20
  • 43