Compare a date and a timestamp with time zone with now() in the same query? I have multiple database servers I'm querying with a query that compares an expiration column with now(). The problem is that one of the servers' expiration column is a timestamp with time zone, and all the rest are simply date. I can't change this because I don't have admin access, and in fact I'm only querying the view. Postgres is fairly new to me, so I don't really understand how the dates and times work with each other.

When I try and query the server with timestamp with time zone by casting the timestamp as a date:

...
WHERE
    (
        status_code = '30000'
        OR status_code = '30005'
    )
AND CAST(expiration AS DATE) &gt; now()


It works, but using the same query on the servers where expiration is already a date fails:


  [Err] ERROR:  invalid input syntax for type date: "No End Date"


Any help would be appreciated, I'd really rather not hard code an exception for this one DB server.
This should never fail (I simplified a bit):

WHERE status_code IN ('30000','30005')
AND   expiration &gt; 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 &gt; 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 &gt; 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')