How do I get this query to work in PostgreSQL?

rubyonrails-talk
1

Query: Ticket.count(:group => 'date(created_at)', :having => ['date_created_at >= ? and date_created_at <= ?', "2010-09-14", "2010-10-13"])

Successful MySQL 5.1.41 output:

SELECT count(*) AS count_all, date(created_at) AS date_created_at FROM `tickets` GROUP BY date(created_at) HAVING date_created_at >= '2010-09-14' and date_created_at <= '2010-10-13'

PostgreSQL 8.4.5 error:

ActiveRecord::StatementInvalid (PGError: ERROR: column "date_created_at" does not exist LINE 1: ...FROM "tickets" GROUP BY date(created_at) HAVING date_creat...                                                              ^ : SELECT count(*) AS count_all, date(created_at) AS date_created_at FROM "tickets" GROUP BY date(created_at) HAVING date_created_at >= '2010-09-14' and date_created_at <= '2010-10-13' )

Many thanks for the help!

2

Try:

Ticket.count(:group => ‘date(created_at)’, :having => [‘date(created_at) >= ? and date(created_at) <= ?’, “2010-09-14”, “2010-10-13”])

HTH

3

Erol, that worked for PostgreSQL. However, it fails in MySQL:

Mysql::Error: Unknown column 'created_at' in 'having clause': SELECT count(*) AS count_all, date(created_at) AS date_created_at FROM `tickets` GROUP BY date(created_at) HAVING date(created_at) >= '2010-09-14' and date(created_at) <= '2010-10-13'

Is this just one of those queries that simply cannot be database agnostic?

Thanks for the help.

4

AFAIK yes.