current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 0 down vote favorite

Using this query:

users = User.where('confirmed_at is NULL AND confirmation_sent_at <= DATE_SUB(NOW(), INTERVAL ? days)', 1)

In mysql goes okay but in Postgresql it fails with:

PG::SyntaxError: ERROR:  syntax error at or near "1"
    LINE 1: ...AND confirmation_sent_at <= DATE_SUB(NOW(), INTERVAL 1 day))
                                                                 ^
    : SELECT "users".* FROM "users"
      WHERE (confirmed_at is NULL AND confirmation_sent_at <= DATE_SUB(NOW(), INTERVAL 1 day))

I'm trying to understand but missing the context here. Why is the integer 1 not valid in this query?

share|improve this question
up vote 2 down vote accepted

There is no function DATE_SUB() in PostgreSQL, so it cannot work.

This expression would work in Postgres:

... AND confirmation_sent_at <= (now() - interval '1 day')

Or, if confirmation_sent_at is a date:

... AND confirmation_sent_at <= (now()::date - 1)
share|improve this answer
    
Thanks, I totally missed that DATE_SUB() could have be a mysql function only a bit cleaner than the one below – Rubytastic Nov 8 '13 at 20:27
up vote 1 down vote

Try...

users = User.where(
    "confirmed_at IS NULL " +
    "AND confirmation_sent_at <= (NOW() - INTERVAL '1 DAY')"
)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.