0

I'm building an app for Heroku (Postgres) but for various reasons, developing it locally using MySQL. I've hit a roadblock concerning something that my MySQL dev environment takes with no trouble but that the heroku Postgres environment hangs up on. Here is the log stream from Heroku:

2012-02-07T10:00:00+00:00 app[web.1]: Report Load (2.5ms)  SELECT id, datetime_utc FROM "reports" WHERE (datetime_utc = '2012-02-07 10:00:00.000000') AND (datetime_utc > spot_sunrise_utc AND datetime_utc > spot_sunset_utc) ORDER BY datetime_utc ASC LIMIT 500
2012-02-07T10:00:00+00:00 app[web.1]: PGError: ERROR:  operator does not exist: timestamp without time zone > time without time zone
2012-02-07T10:00:00+00:00 app[web.1]: LINE 1: ...EEN 0.4573170731707317 and 200) AND (datetime_utc > spot_sun...
2012-02-07T10:00:00+00:00 app[web.1]:                                                              ^
2012-02-07T10:00:00+00:00 app[web.1]: HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

The datetime_utc field is a real datetime whereas the spot_sunrise/sunset_utc fields are just time values.

What is causing this, and how do I work around it?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user1051849
  • 2,307
  • 5
  • 26
  • 43

1 Answers1

2

To alleviate the type incompatibility, cast the timestamp value datetime_utc to time:

SELECT id, datetime_utc
FROM   reports
WHERE  datetime_utc = '2012-02-07 10:00'
AND    datetime_utc::time > spot_sunrise_utc 
AND    datetime_utc::time < spot_sunset_utc  -- you mean <, right?
ORDER  BY datetime_utc  -- noise
LIMIT  500;

See:

The added ORDER BY datetime_utc is just noise after filtering with WHERE datetime_utc = '2012-02-07 10:00'.

Aside 1: It is generally a good idea to develop with the same DB locally as you deploy with. So it is a bad idea to use MySQL locally.

Aside 2: The name of the database is PostgreSQL or Postgres for short. No such thing as "postgre".

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • hi Erwin - did you mean "CAST(datetime_utc as time)"? the problem is that i actually need the whole datetime info – user1051849 Feb 07 '12 at 10:33
  • If you want to compare the `timestamp` to a time, you'll have to cast it to `time`. The first WHERE condition uses the full timestamp anyway. And yes, `datetime_utc::time` is the same as `CAST(datetime_utc as time)`. – Erwin Brandstetter Feb 07 '12 at 10:39
  • ok great thanks for the clarifications. unfortunately i am painfully aware of the problems involved in maintaining different dbs for dev and production - hoping to get that resolved as soon as possible. – user1051849 Feb 07 '12 at 11:38