6

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.

  • Are you sure expiration is a timestamp column? Sounds like it's a convertion problem caused by the CAST operator – a_horse_with_no_name Apr 3 '12 at 21:46
8

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')
  • I get the following error with your query: [Err] ERROR: operator does not exist: text > timestamp with time zone. This was my original query on all databases until I ran into this original error when querying this one machine with expiration stored as a timestamp+timezone. I'm also querying a view, does the view convert datatypes to text or something? – Chris Apr 4 '12 at 12:20
  • @Chris: I added a bit to my answer. – Erwin Brandstetter Apr 4 '12 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.