Time comparison inconsistency with SQLite, PostgreSQL and MySQL with ActiveRecord

gistfile1.rb

  def pending_notifications
    notifications = self.notifications

    if last_notification_read_at

      # Sqlite doesn't properly compare dates because doesn't have a dedicated
      # type for it.
      # TODO: Investigate further and find a better solution for this.
      #
      time_restriction = if connection.class.name.demodulize =~ /SQLite/
        ["datetime(notifications.created_at) >= ?", last_notification_read_at]
      elsif connection.class.name.demodulize =~ /PostgreSQL/
        ["notifications.created_at >= ?", last_notification_read_at.to_time]
      else
        ["notifications.created_at >= ?", last_notification_read_at]
      end

      notifications = notifications.where(time_restriction)
    end

    notifications
  end

Seriously, doesn't anyone have a good solution for this? I've been struggling with it for the last few hours.

Which version of Ruby are you using?

BTW:

time_restriction = case connection.class.name.demodulize 
                   when /SQLite/
                     ["datetime(notifications.created_at) >= ?", last_notification_read_at]
                   when /PostgreSQL/
                     ["notifications.created_at >= ?", last_notification_read_at.to_time]
                   else
                     ["notifications.created_at >= ?", last_notification_read_at]
                   end

What do you have in last_notification_read_at?

Hey! Nice to see you!

last_notification_read_at is DateTime object (from an attribute)

How do you store stuff in SQLite3? Run this in the SQLite3 console.

SELECT created_at, typeof(created_at) FROM entries;

I guess you have a BLOB there when it should be text.

``` X'323031312D30392D30392031363A32353A34342E333233393433'","blob" ``` Wow, that's true. But it seems that BLOB is what SQLite3Adapter uses when dealing with datetimes Josep Jaume

…

On Fri, Sep 9, 2011 at 5:57 PM, Francesc Esplugas < ***@***.***>wrote: How do you store stuff in `SQLite3`? Run this in the `SQLite3` console. SELECT created_at, typeof(created_at) FROM entries; I guess you have a `BLOB` there when it should be `text`. ## Reply to this email directly or view it on GitHub: https://gist.github.com/1206106

See this commit: rails/rails@20c07170
And this rails/rails#2892