Compare a date and a timestamp with time zone with now() in the same query?

Question

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

Answer 1

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. Your question is not telling the whole story.

I wrote a detailed answer to explain the handling of timestamps with or without time zone in PostgreSQL recently - if that should be an issue here.

---

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 with a CASE statement like

...
AND   CASE WHEN expiration::text = 'No End Date' THEN 'infinity'::timestamp
           WHEN expiration::text = 'foo' THEN '-infinity'::timestamp
           ELSE expiration::timestamp
      END > now()

More about the special value infinity in the manual.

The cast to text (::text) is redundant with text, but makes the expression work with date / timestamp values as well.

Use the AT TIME ZONE construct if expiration is supposed to be a local timestamp of another time zone:

expiration::timestamp AT TIME ZONE '+3'

If expiration is in ambiguous / non-standard format, use to_timestamp():

to_timestamp(expiration::text, 'yyyy-mm-dd')