Search code examples
postgresqlruby-on-rails-4rails-activerecordrails-postgresqlpostgresql-9.3

Find records where a timestamp is present

I'm migrating from SQLite to PostgreSQL, and the following query is not working anymore:

where("my_timestamp is NOT NULL and my_timestamp != ''")

How can I find all records that have a certain (datetime) attribute present?

Solution

  • Assuming that your my_timestamp column is a real timestamp (i.e. t.datetime in ActiveRecord parlance) then a simple NOT NULL test is sufficient:

    where('my_timstamp is not null')

    If this is the case then your query should be giving you an error like:

    invalid input syntax for type timestamp: ""

    pointing at your my_timestamp != '' test. Your comparison with an empty string worked fine in SQLite because SQLite doesn't have a real timestamp type, it just uses ISO 8601 formatted strings in text columns; this data type problem is also why you ended up with '' in your timestamp columns in SQLite in the first place.