Postgresql – Is it possible to have xid before and after wrap around with the same timestamp

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:

• Ignoring timezones altogether in Rails and PostgreSQL

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

• How to convert “string” to “timestamp without time zone”

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

Select A.status,count (A.record_id),sum(A.hits)
from 
(Select distinct status,record_id,hits from YourTable) A
group by A.status