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) > 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 raise an exception with an actual date value:

...
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 assuming '00:00' hours.

The error message tells a different story. You are actually trying to input a date with invalid syntax.

About handling timestamps with or without time zone in PostgreSQL:

• Ignoring timezones altogether in Rails and PostgreSQL

As it turns out, your expiration is a column of type text. You need to cast it to date or timestamp (whichever fits your need). For valid format:

...
AND   expiration::timestamptz > now()

Invalid strings like 'No End Date' in that text column need to be cleaned or treated specially in a CASE construct. Like:

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

The manual about the special value infinity.

If the format of the timestamp literal can be ambiguous, use to_date() or to_timestamp() and define the format explicitly:

to_date('07/08/2013', 'DD/MM/YYYY')
to_timestamp(expiration, 'yyyy-mm-dd')

• 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 a given time zone:

expiration::timestamp AT TIME ZONE 'UTC'  -- desired time zone here

StackExchange Database Administrators Q#16036, answer score: 13