Time/datetime Comparison in PostgreSQL (Heroku)

Question

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 · Accepted Answer · 2021-04-23 08:20:24Z

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:

Postgres data type cast

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".

edited Apr 23, 2021 at 8:20

answered Feb 7, 2012 at 10:22

Erwin Brandstetter

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

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

3 Comments

user1051849 Over a year ago

hi Erwin - did you mean "CAST(datetime_utc as time)"? the problem is that i actually need the whole datetime info

Erwin Brandstetter Over a year ago

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).

user1051849 Over a year ago

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.

Collectives™ on Stack Overflow

Time/datetime Comparison in PostgreSQL (Heroku)

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related