Database agnostic now() datetime function?

Question

I've the following condition in one of my model which works for mysql.

with_scope :find => { :conditions => "starts_at <= now() AND ends_at >= now()" }

But I get error on the sqlite3 test database coz that now() is specific to mysql db only. What would be a db agnostic way for that now() function?

Answer 1

CURRENT_TIMESTAMP is standard in SQL92, and should be sufficiently cross-platform.

Answer 2

Or you can use ruby to give you the current timestamp and not rely on the database. eg.

current_ts = Time.now
with_scope :find => { :conditions => ["starts_at <= ? AND ends_at >= ?", current_ts, current_ts] }

I personally prefer this as the timestamp can be modified easily (plus or minus an hour etc.) without having to write sql which may not work in all versions.