10

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.

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

1 Answer 1

13

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:


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

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
2
  • 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, 2012 at 12:20
  • @Chris: I added a bit to my answer. Apr 4, 2012 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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