How do I get this query to work in PostgreSQL?

Rails
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

On Fri, Oct 15, 2010 at 11:20 AM, Lee S. [email protected] wrote:

http://groups.google.com/group/rubyonrails-talk?hl=en.

–
Erol M. Fornoles

http://twitter.com/erolfornoles
http://ph.linkedin.com/in/erolfornoles

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

On Fri, Oct 15, 2010 at 11:46 AM, Lee S. [email protected] wrote:

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

AFAIK yes.

–
Erol M. Fornoles

http://twitter.com/erolfornoles
http://ph.linkedin.com/in/erolfornoles