PostgreSQL in Rails query is not working

Question

New to PostgreSQL (and only 6 months more experienced in programming in general), having problems with following query:

TutoringSession.where("extract (DOW from begin_time)
                     = extract DOW from (?)", Time.now)

Gives me the following error message:

ActiveRecord::StatementInvalid: PG::Error: ERROR:  syntax error at or near "DOW"
LINE 1: ..."  WHERE (extract (DOW from begin_time) = extract DOW from (...
                                                             ^
: SELECT "tutoring_sessions".* FROM "tutoring_sessions"  WHERE (extract (DOW from   begin_time) = extract DOW from ('2012-04-18 14:39:58.202249'))

I have looked through the PostgreSQL documentation. Any ideas?

Erwin Brandstetter · Accepted Answer · 2012-04-18 15:18:54Z

2

^{Updated after comment.}
You misplaced a parenthesis. And add an explicit type cast.

TutoringSession.where("extract (DOW from begin_time)
                     = extract (DOW from timestamp ?)", Time.now)

If you just want to insert the current time, you can let PostgreSQL do that for you - provided the time on the database server works for you. Consider time zones.

TutoringSession.where("extract (DOW from begin_time)
                     = extract (DOW from now())")

The manual about extract().

edited Apr 18, 2012 at 15:18

answered Apr 18, 2012 at 14:58

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user924088 Over a year ago

TutoringSession.where("extract (DOW from begin_time) = extract (DOW from ?)", Time.now) TutoringSession Load (2.2ms) SELECT "tutoring_sessions".* FROM "tutoring_sessions" WHERE (extract (DOW from begin_time) = extract (DOW from '2012-04-18 15:11:55.832108')) ActiveRecord::StatementInvalid: PG::Error: ERROR: function pg_catalog.date_part(unknown, unknown) is not unique LINE 1: ...sessions" WHERE (extract (DOW from begin_time) = extract (D... HINT: Could not choose a best candidate function. You might need to add explicit type casts.

Collectives™ on Stack Overflow

PostgreSQL in Rails query is not working

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related