This should never fail (I simplified a bit):
WHERE status_code IN ('30000','30005')
AND expiration > now()
PostgreSQL can compare date and timestamp (with or without time zone) automatically. If one is a date it is cast to timestamp automatically (0:0 hours).
The error message tells a different story. You are actually trying to input a date with invalid syntax.
I wrote a detailed answer about handling of timestamps with or without time zone in PostgreSQL recently - if that should the issue:
As it turns out, expiration is a text column. You need to cast it to date or timestamp (whichever fits your need). If it is in a valid format:
...
AND expiration::timestamptz > now()
If you have invalid strings like 'No End Date' in that text column, you need to clean the source or treat those specially in a CASE construct:
...
AND CASE WHEN expiration::text = 'No End Date' THEN 'infinity'::timestamp
WHEN expiration::text = 'foo' THEN '-infinity'::timestamp
ELSE expiration::timestamp
END > now()
The manual about the special value infinity.
The cast to text (::text) is redundant with text, but makes the expression work with date / timestamp values as well.
If the format of the time stamp literal can be ambiguous, use to_date() or to_timestamp() and define the format explicitly:
to_date('07/08/2013', 'DD/MM/YYYY')
Use the AT TIME ZONE construct if expiration is supposed to be a local timestamp of another time zone:
expiration::timestamp AT TIME ZONE 'UTC' -- desired time zone here
If expiration is in ambiguous / non-standard format, use to_timestamp():
to_timestamp(expiration::text, 'yyyy-mm-dd')
Best Answer
If you want an index to be used, you should remove the function from the column. You can rewrite the condition (which removes the time part from the two datetimes):
to the equivalent (where the function is applied only to
sysdateand not the column):This way, the condition is "sargable" and an index on
timefieldcan be used. With the previous condition, a table scan (or a full index scan) was required.